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.

Answer 1

You need connection strings

Answer 2

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.

Answer 3

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