How do I connect to an MS Access database using Perl?

Question

I have a .accdb file on my local machine and I am trying to connect to it and read some data from 3 tables within the DB. How do I establish the connection using Perl?

So far I have scraped together this much for MS Access, but I am getting errors saying that I am not using the correct driver. Any ideas?

my $msaccess_dbh = DBI->connect(
    'dbi:ODBC:driver=microsoft access driver (*.accdb);' .
    'dbq=C:\path\to\database\databasefile.accdb'
);

Thanks!

EDIT: Just to clarify, I have no real requirements here. I just need to do 2 or 3 selections from this MS Access DB, and then I will be done with it. So any help with connecting and selecting would be great. Thanks again.

Solution 1

Based on your connection string it looks like you are (a) on Win32 and (b) connecting to a database on your local machine. If I am correct why bother with ODBC when you can connect directly with Jet? Refer below:

#!/usr/bin/perl
use strict;use warnings;

use Win32::OLE;

my $DBFile  = qw( X:\Path\To\Your\Database.mdb ); # 
#Choose appropriate version of Jet for your system
my $Jet = Win32::OLE->CreateObject('DAO.DBEngine.36')   or die "Can't create Jet database engine.";
my  $DB = $Jet->OpenDatabase( $DBFile );

my $SQLquery = "DELETE * FROM Test_Table";
$DB->Execute($SQLquery, 128); #128=DBFailOnError

Solution 2

I'm guessing the driver didn't match what you had for the DSN, or the other thing that causes problems is if you're mixing 64-bit Perl with a 32-bit ODBC driver, or 32-bit Perl with a 64-bit driver. The real problem is that error message, it's terribly vague -- you think maybe they could tell you whether the data source OR the driver was the problem? In a perfect world...

Anyway, that method you were trying does work if your DSN is correct, & if your Perl & ODBC driver are in the same bit family.

The driver reference in the DSN has to match exactly what's listed under Adminstrative Tools > Data Sources (ODBC) > Drivers tab. Mine is listed as Microsoft Access Driver (.mdb, .accdb) so that's slightly different from what you had. In Perl the line to connect is:

my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=X:\Path\To\Your\Database.mdb')

More info on MS Access with Perl on Windows 7 is here.

Solution 3

You need connection strings

Solution 4

I've successfully used connection strings with that format in the past, but that was for the old *.mdb format. It's possible that your ODBC driver doesn't support the newer *.accdb format in Access 2007.

View more solutions

Comments

[spoulson]

DBI->connect("dbi:ODBC:$connection_string");

[Noslide]

so something like this: my $MSACCESS_DSN ='Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\path\to\database\databasefile.accdb;Persist Security Info=False;'; my $msaccess_dbh = DBI->connect("dbi:ODBC:$MSACCESS_DSN"); Because that is throwing an error saying data source name not found and no default driver specified. Sorry I am somewhat of a MS db noob.

[Noslide]

I converted it to *.mdb but it still refuses to work. I may have to look into getting a different driver.

[heferav]

That's wby it says "choose appropriate Version" on the comment line.

[Noslide]

Alright this seems to work, my question to you now is how do I save a selection to an array using this method?