A windsurfing, CSS-grudging, IE-hating, web-developing, gigantic-machine-puzzling blog

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.

Previous

China & The South China Sea: Are You Fucking Kidding?

Next

Streaming Video To iPhone/iPad App Review

4 Comments

  1. johnpeterdinesh

    Its works great . thanks,

  2. Eric Hansen

    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.

  3. Jerrad

    I’ve been fighting with this for hours, and hit upon the same incantation as you, but no matter what format I try for the file path (relative, UNC, absolute DOS, absolute-*NIX [forward slashes]) I get the vague error “Not connected to a database” when I try to use the returned DBH, even though the connection does not fail; the DBH is an empty, blessed hashref. Any suggestions? What format of path has worked for others?

    • Wick

      It was 3 years ago when I was using this, it was an absolute DOS path:

      my $dbh_mdb = DBI->connect(‘dbi:ODBC:driver=Microsoft Access Driver (*.mdb, *.accdb);dbq=d:\ncap\NCAP.mdb’) or die “\nCan’t connect to Access database: ” . $DBI::errstr;

      Hope that helps. I remember this being really frustrating.

Leave a Reply

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

Powered by WordPress & Theme by Anders Norén