vdubjunkie has asked for the wisdom of the Perl Monks concerning the following question:

Hello,

I am a perl pup, but I'm able to make stuff work pretty well. I've delved into attempting to work with a MS Access database, and I'm hitting a brick wall. This is also my first MS PERL experience.

I've seen this error addressed plenty of times, but none of the answers have seemed to offer me any real assistance. Firstly, I can open this db in Access with no problem.

So, with the code below, I get the response listed below it. 

#!"c:\documents and settings\user\perl\bin\perl"

use DBI;
use strict;

my $adb = 'testdb.mdb';
my $driver = 'driver=Microsoft Access Driver(*.mdb);dbq=';
my $dstring = $driver . $adb;

my $dbh = DBI->connect("DBI:ODBC:$dstring","","");
#       || die; # "$DBI::errstr";

my @avail = DBI->available_drivers;
print "--$_--\n" foreach(@avail);

my %drivers = DBI->installed_drivers();

while (my($k,$v)=each(%drivers))
{
        print "Driver:$k\t-\t$v\n";
}

$dbh->disconnect;

[download]

Output to screen below

DBI connect('driver=Microsoft Access Driver(*.mdb);dbq=testdb.mdb','',
+..
.) failed: [Microsoft][ODBC Driver Manager] Data source name not found
+ and no de
fault driver specified (SQL-IM002)(DBD: db_login/SQLConnect err=-1) at
+ C:\temp\t
emp.pl line 14
--DBM--
--ExampleP--
--File--
--Gofer--
--ODBC--
--Proxy--
--SQLite--
--Sponge--
Driver:ODBC     -       DBI::dr=HASH(0x1a4d5c4)
Can't call method "disconnect" on an undefined value at C:\temp\temp.p
+l line 27.

[download]
Any help with this is GREATLY appreciated

Replies are listed 'Best First'.
Re: MS Access database problem
by tachyon-II (Chaplain) on Nov 30, 2007 at 02:57 UTC

    You can't connect to an access database simply by specifying the filename of the database file "testdb.mdb". You need to register it as a datasource and then you connect to the registerd name. See this tutorial for the details about how to register it with the ODBC controller or just do Start|Run|odbcad32.exe. In the widget select driver for *.mdb, add/configure giving your DB a name and selecting the path to your mdb file. You connect to the name you just assigned, rather than the blah.mdb file per se.

    cheers

    tachyon

[reply]
      I am ASTOUNDED

      to learn that EVERY page I found led me to believe you could simply access it by the filename. Believe it or not, I searched high and low prior to posting here, and I never saw a page even reference HOW to open a MS Access database by it's DSN rather than it's name...

      Needless to say..

      That worked!!

      A huge thank you

      ..oh, and no, the space before (*.mdb) didn't matter as doesn't capitalization on DBI, etc.. for those searching these forums later.

[reply]

        Actually having just read the pod for DBD::ODBC they have introduced direct access to mdb files since last I looked (6 years ago more or less :-). I think your original code would have worked if you had specified the full path to the testdb.mdb file. See DBD-ODBC/ODBC.pm#Connect_without_DSN

        Anyway glad you got it working. As noted in the DBD::ODBC pod if you use the DSN method that worked for you then you need to make it a system DSN, not a user DSN.

[reply]
Re: MS Access database problem
by roboticus (Chancellor) on Nov 29, 2007 at 16:28 UTC
    vdubjunkie:

    I don't see how that program returned that error message, as the database name in the error doesn't match the one in your program. Anyway, I use the ODBC driver all the time (though never with access). I'd suggest placing curly braces around the value in the 'driver=' clause, like so:

    my $driver = 'driver={Microsoft Access Driver(*.mdb)};dbq=';

    [download]
    Give that a shot and let's see if the error changes. If so, let me know what the new error message is...

    ...roboticus

[reply]
[d/l]
      oops, looks like my manual search/replace didn't function well. Now that error looks right :P

      So, I changed like you showed, and no dice. Same error

      Any other thoughts?

[reply]
        vdubjunkie:

        OK then for the next step, let's just try to change the error message. Change the line:

        my $dbh = DBI->connect("DBI:ODBC:$dstring","","");

        [download]
        to 
        my $dbh = DBI->connect("DBI:ODBC:driver={SQL Server};"
        . "SERVER=Bobs.pancake.hut;DATABASE=flapjacks;",
        "account","password");

        [download]
        Hopefully, instead of the error you're getting, you'll get one about not being able to connect.

        ...roboticus

[reply]
[d/l]
[select]
Re: MS Access database problem
by rhesa (Vicar) on Nov 29, 2007 at 22:39 UTC
    On my system, the ODBC driver name has a space between "Driver" and "(*.mdb)". Maybe yours does too? I believe it has to match exactly with what the control panel for ODBC connections lists.
[reply]

Back to Seekers of Perl Wisdom