MS Access ODBC with Windows 7 64-bit and Perl

MS Access ODBC 64-bit Windows 7 driverJust writing that headline’s enough to make me cringe. Hell, I almost gave up at the thought of dealing with the ODBC error in the first place:

Can't connect to Access database: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at [script name] line [line number].

But turns out it’s not so bad, just a little tricky.

An important detail: I’m using Strawberry Perl 64-bit. It’s v5.12.3 multi-thread, but that doesn’t really matter. 64-bit OS, 64-bit Perl, 64-bit ODBC driver … don’t mix 64s with 32s & everything is good in the world. If you’re trying to use some twisted combination, this blog post may not be quite as helpful. But who knows? You’ll find out soon enough.

First step is make sure the MS Access 64-bit ODBC driver is present & functioning: Administrative Tools > Data Sources (ODBC), which brings up the ODBC Administrator. Switch to the Drivers tab to view the installed 64-bit drivers.

(Note to 32-bit-driver-on-64-bit-OS-type people: to see your installed 32-bit ODBC drivers, you’ll need to run the 32-bit ODBC Administrator instead. Tricky or what?)

My copy of Windows 7 only had the SQL Server ODBC driver listed. Head over to Microsoft’s website to download the MS Access 64-bit ODBC driver.

Once that’s installed, pull up your Data Sources window again & a bunch of new drivers will be listed on the Drivers tab. In my Perl program I’m using DBI, so I need the driver name exactly as it appears on the Drivers tab:

Microsoft Access Driver (*.mdb, *.accdb)

So in Perl-ese, that becomes:

use DBI;
my $dbh = DBI->connect('dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=[path to mdb file]') or die "\nCan't connect to Access database: " . $DBI::errstr;

Get the path right & it should work! Hope this helps someone.

This entry was posted in perl, Windows 7. Bookmark the permalink.

2 Responses to MS Access ODBC with Windows 7 64-bit and Perl

  1. johnpeterdinesh says:

    Its works great . thanks,

  2. Eric Hansen says:

    I create MS-Access 4.x files (*.MDB) via ODBC Admin appl. on Windows 7.
    Then I use Perl Win32:ODBC (ActivePerl 5.6.1) to create the tables, constraints(indexes).
    I can run over 500 simultaneous detached background processes on my laptop that access the same *.MDB file, each process creating it’s own output file from the SQL query it ran. Runs in seconds. I have also ran 66 simultaneous detached background processes which update 66 different logical sections of the same table concurrently without stepping on each other, for about 1200 rows updated. Not sure the real limits, but these trails show the Jet Engine factory installed with Windows 7 O/S does a great job at concurrent processing.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>