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

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.

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

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.

You need connection strings

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.