How to connect to MS Access database using Perl?

Question

I have a .accdb file on my local machine, and I'm trying to connect to it and read some data from 3 tables in the database. How to establish a connection using Perl?

So far I have been versed in MS Access, but I get 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 make 2 or 3 choices from this MS Access database, and then I will work with it. Therefore, any help with connecting and choosing will be excellent. Thanks again.

Answer 1

Depending on your connection string, it looks like this: (a) on Win32 and (b) connecting to the database on your local computer. If I'm right, why bother with ODBC when you can connect directly to Jet? See 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 

Answer 2

I assume that the driver does not match what you had for the DSN, or something else that causes problems if you mix 64-bit Perl with a 32-bit ODBC driver or 32-bit Perl with a 64-bit driver. The real problem is that the error message is terribly vague - you think maybe they could tell you if the data source or driver was the problem? In an ideal world ...

In any case, this method that you tried to do works if your DSN is correct, and if your Perl and ODBC driver are in the same bit family.

The link to the driver in DSN should exactly match what is specified in the Installation Tools > Data Sources (ODBC) > Drivers section. Mine is listed as a Microsoft Access Driver (.mdb, .accdb), so it’s a bit different than what you had. In Perl, the connection string is:

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

Additional information about MS Access with Perl in Windows 7 is here .

Answer 3

You need connection strings

Answer 4

I have successfully used connection strings with this format in the past, but this was for the old * .mdb format. Your ODBC driver may not support the new * .accdb format in Access 2007.