will_danger has asked for the wisdom of the Perl Monks concerning the following question:

OS: Windows 7 Perl: 5.14.2 via Strawberry Perl I am trying to write a simple Perl script to read in a .csv file and import it into an Access 2010 (.accdb) file. Try as I may, I cannot get my perl script to connect to the database file. Relevant code is below: 
#!C:/strawberry/perl/bin/perl.exe

use strict;

use DBI;
use Text::CSV;
use Win32::OLE;

my $DBFile = "KosJourney.accdb";
my $DBHandle = DBI->connect("dbi:ADO:Provider=Microsoft.ACE.OLEDB.12.0
+;Data Source=$DBFile;Persist Security Info=False;") or die $DBI::errs
+tr;

.....

open my $FILEHANDLE,$FilePath or die "Failure opening CSV file for rea
+ding: " . $!;
while (<$FILEHANDLE>)
{
    chomp;
    next if /Last Name/;
    my ($LastName,$FirstName,$GUID) = split (/\t/,$_);
    $LastName =~ s/"//g;
    $FirstName =~ s/"//g;
    $GUID =~ s/"//g;
    my $FullName = $FirstName . " " . $LastName;
    
    my $InsertUserQuery = "INSERT INTO Users (Username,FullName,FirstN
+ame,LastName) VALUES (?,?,?,?);";
    
    &Echo("Executing SQL: " . $InsertUserQuery);
    
    my $InsertUserResult = $DBHandle->prepare($InsertUserQuery);
    $InsertUserResult->execute($GUID,$FullName,$FirstName,$LastName);
}
close $FILEHANDLE;

[download]
The error follows: 
DBI connect('Provider=Microsoft.ACE.OLEDB.12.0;Data Source=KosJourney.
+accdb;Persist Security Info=False;','',...) failed: Can't Open Connec
+tion 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=KosJourney.accdb;
+Persist Security Info=False;'

Package    : DBD::ADO::dr
Filename   : C:/strawberry/perl/vendor/lib/DBD/ADO.pm
Line       : 158
Last error : -2146824582

OLE exception from "ADODB.Connection":

Provider cannot be found. It may not be properly installed.

Win32::OLE(0.1709) error 0x800a0e7a
in METHOD/PROPERTYGET "Open" at PopulateUsersFromResultCSV.pl line 21
Can't Open Connection 'Provider=Microsoft.ACE.OLEDB.12.0;Data Source=K
+osJourney.accdb;Persist Security Info=False;'

[download]
It is the connection string throwing the exception. I have access 2010 installed. I'm not sure if it is necessary but I have also installed MDAC (http://www.microsoft.com/en-us/download/confirmation.aspx?id=5793) as well as Microsoft Access Database Engine 2010 Redistributable (http://www.microsoft.com/en-us/download/details.aspx?id=13255). The weird thing is, this exact setup is working just fine on other computers (which are identical in configuration and hardware). The .adddb file is sitting next to this script into the same folder, to eliminate path issues. All of the modules noted in the code have been successfully installed via CPAN. I have and still am scouring the web for help on this, so far nothing has been forthcoming. Note that I don't really care how the connection is made, I am using DBI because that is what I am familiar with. I do not have a DSN set up on my local machine but I didn't beleive that to be necessary. Any help anyone can offer will be really appreciated.

Replies are listed 'Best First'.
Re: How can I connect my perl script to an Access 2010 (.accdb) file?
by roboticus (Chancellor) on Jun 02, 2012 at 23:53 UTC

    danger_will_robinson:

    Have you installed DBD::ADO? The DBI driver needs an appropriate DBD interface for your database.

    Also, if you move your prepare statement before the loop, it may make things go a bit quicker:

    open my $FILEHANDLE,$FilePath or die "Failure opening CSV file for rea
+ding: " . $!;
my $InsertUserQuery = "INSERT INTO Users (Username,FullName,FirstName,
+LastName) VALUES (?,?,?,?);";
my $InsertUserResult = $DBHandle->prepare($InsertUserQuery);
while (<$FILEHANDLE>)
{
    chomp;
    next if /Last Name/;
    my ($LastName,$FirstName,$GUID) = split (/\t/,$_);
    $LastName =~ s/"//g;
    $FirstName =~ s/"//g;
    $GUID =~ s/"//g;
    my $FullName = $FirstName . " " . $LastName;
    
    &Echo("Inserting $FullName");
    $InsertUserResult->execute($GUID,$FullName,$FirstName,$LastName);
}
close $FILEHANDLE;

    [download]

    ...roboticus

    You bubble-headed booby! --Dr. Smith

    (Sorry for the cheap joke.)

[reply]
[d/l]
      Yep, I just double checked and CPAN tells me that I have the most up to date version (2.99).
[reply]

        will_danger:

        Then I'm guessing the "Microsoft.ACE.OLEDB.12.0" bit isn't installed on your machine. I did a quick google for "ado connection string access" and found http://www.connectionstrings.com/access. Perhaps using "Microsoft.Jet.OLEDB.4.0" may work? (I don't use Access, so I'm not familiar with those connection strings.)

        ...roboticus

        When your only tool is a hammer, all problems look like your thumb.

[reply]
Re: How can I connect my perl script to an Access 2010 (.accdb) file?
by NetWallah (Canon) on Jun 03, 2012 at 00:52 UTC
    I found this connection string in some old code I wrote - it worked at that time (circa 2005) .... 
    use DBI;
use DBD::ODBC;

my $DBFile= q(TestLog.mdb); 

if (-e $DBFile){
   print "Using existing database:$DBFile\n";
}
my $DSN = "driver=Microsoft Access Driver (*.mdb);dbq=$DBFile";
my $dbh = DBI->connect("dbi:ODBC:$DSN", undef,undef) or die "$DBI::err
+str\n";

    [download]

                 I hope life isn't a big joke, because I don't get it.
                       -SNL

[reply]
[d/l]

      I gave it a shot, no luck unfortunately. I've now re-installed Strawberry a few times (trying different perl versions, while fetching fresh modules each time) and installed IIS (because I figured that maybe there were components there I might have needed), aside from trying out your snippet (and installing the necessary module). The error I got was:

      "DBI connect('driver=Microsoft Access Driver (*.mdb);dbq=KosJourney.mdb',',...) failed: MicrosoftODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002) at PopulateUsersFromResultCSV.pl line 23"
[reply]

        I've got it!

        The problem was architecture. Even though my OS is 64 bit, my Office install (which is what I guess determines the architecture of the data access components), is 32 bit. There seemed to have been a communication issue where DBI interacted with the native data access stuff through Microsoft. I uninstalled Strawberry Perl 64 bit, deleted the CPAN modules (since it leaves it there after uninstall), and re-installed SP 32-bit. Then re-downloaded and re-compiled the CPAN modules and...there it is!

        All of my queries are running happily now. Thanks to those who chimed in. On to the next crisis.

[reply]
Re: How can I connect my perl script to an Access 2010 (.accdb) file?
by Anonymous Monk on Jun 26, 2017 at 18:45 UTC

    Have you ever thought of using an ISAM-like database instead of Access? Soooo simple to connect to. No MDAC (Microsoft Data Access Components) required. Your problem is a good example to leave the world of SQL connections and go to a Key/Value pair ISAM-like/NoSQL system.

    Simply unload your MS-Access data to Flat Files (fixed-length "text" records), then build you some indexing to those records using Perl SDBM key/value pairs. The value is the record offset in bytes to seek to (position the file pointer) for random access.

    You will be persistently storing the key/value pairs in a hard disk hash table which can be tied to a Perl program hash table.

    SEE: Joint+Database+Technology at perlmonks.org

[reply]

      So Re^3: Joint Database Technology and ever thought.... raise some issues about the solution you keep advertising. Seems like it has many disadvantages over one of the many other database product out there. Also, each time you post several links are displayed suggesting choosing good node titles, how to link to things etc.

[reply]

Back to Seekers of Perl Wisdom