How can I connect my perl script to an Access 2010 (.accdb) file?

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.

Comment on How can I connect my perl script to an Access 2010 (.accdb) file? Select or Download Code
Re: How can I connect my perl script to an Access 2010 (.accdb) file? by roboticus (Canon) 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]
Re^2: How can I connect my perl script to an Access 2010 (.accdb) file? by Anonymous Monk on Jun 02, 2012 at 23:55 UTC
Yep, I just double checked and CPAN tells me that I have the most up to date version (2.99).	[reply]
Re^3: How can I connect my perl script to an Access 2010 (.accdb) file? by roboticus (Canon) on Jun 03, 2012 at 00:18 UTC
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^4: How can I connect my perl script to an Access 2010 (.accdb) file? by will_danger (Initiate) on Jun 03, 2012 at 00:33 UTC
Re: How can I connect my perl script to an Access 2010 (.accdb) file? by NetWallah (Monsignor) 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]
Re^2: How can I connect my perl script to an Access 2010 (.accdb) file? by will_danger (Initiate) on Jun 03, 2012 at 02:06 UTC
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: Microsoft ODBC Driver Manager Data source name not found and no default driver specified (SQL-IM002) at PopulateUsersFromResultCSV.pl line 23"	[reply]
Re^3: How can I connect my perl script to an Access 2010 (.accdb) file? by will_danger (Initiate) on Jun 03, 2012 at 03:59 UTC
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^4: How can I connect my perl script to an Access 2010 (.accdb) file? by afoken (Parson) on Jun 03, 2012 at 15:44 UTC


more useful options
	PerlMonks

How can I connect my perl script to an Access 2010 (.accdb) file?

The best material for plates (tableware) is: