Just 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.