Connecting to MS Access

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

I am trying to connect to access and retrieve some information out of the database. But the problem is that I cannot get connected to the Database. I just dl the newest version of perl and installed DBI and DBD-ODBC. I believe I installed the correct drivers but I am receiving this error.

(DBI connect('dbName",'',...) failed:
[Microsoft][ODBC Driver Manager]
Data source name not found and no default driver specified
(SQL-IM002)(DBD: db_login/SQLConnect err=-1)
at Filename.plx line 5
Can't call method "prepare" on an undefined value at Filename.plx line
+ 8.
[download]

These are the errors I am getting. The code is as follows:

#Windows-based Perl/DBI/MS Access example
use DBI;

 #open connection to Access database
 $dbh = DBI->connect('dbi:ODBC:TestCasesXP2K','','');

 #prepare and execute SQL statement
 $sth = $dbh->prepare('SELECT * FROM [TestCasesOutput]');
 $sth->execute || 
       die "Could not execute SQL statement ... maybe invalid?";
print "Error 4\n";
 #output database results
 while (@row=$sth->fetchrow_array)
  { print "@row\n" }
[download]

_{janitored by ybiC: Balanced <code> tags around codeblock, to facilitate code download}

Comment on Connecting to MS Access Select or Download Code

Replies are listed 'Best First'.
Re: Connecting to MS Access by keszler (Priest) on Aug 12, 2004 at 16:25 UTC
I'd guess that there's no ODBC DSN called "TestCasesXP2K" set up on your PC, or if one exists it isn't a System DSN and isn't available to the current user. Try this instead: `DBI->connect( "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=C:\ +\path\\filename.mdb", "", "" );` [download] replacing "path" with the path and "filename" with the name of an Access database file. The two empty parameters at the end are username and password - if the database requires such fill them in also.	[reply] [d/l]
Re: Connecting to MS Access by strat (Canon) on Aug 13, 2004 at 06:26 UTC
If you use a user DSN and then run the script as another user (e.g. with CGI): better use a system DSN if possible But with access (and many other databases, you don't need a DSN in the DSN-Manager, just specify the file (with path) and the driver in your connect string, e.g. `my $DSN = 'driver=Microsoft Access Driver(.mdb);dbq=d:\\path\\to\\dat +abase.mdb'; my $dbh = DBI->connect("dbi:ODBC:$DSN", '','') or die "$DBI::errstr\n";` [download] This and more is written in perldoc DBD::ODBC ... Best regards, perl -e "s>>F>e=>y)\*martinF)stronat)=>print,print v8.8.8.32.11.32"	[reply] [d/l]
Re: Connecting to MS Access by inman (Curate) on Aug 12, 2004 at 16:19 UTC
Wow! I tried your code and got the same thing! `DBI connect('TestCasesXP2K','',...) failed: [Microsoft][ODBC Driver Ma +nager] Data source name not found and no default driver specified (SQ +L-IM002)(DBD: db_login/SQLConnect err=-1) at C:\Daniel\perl\PerlMonks +\dbitest.pl line 13 Can't call method "prepare" on an undefined value at C:\Daniel\perl\Pe +rlMonks\db itest.pl line 16.` [download] I suspected that I might since I haven't set up an ODBC connection using the ODBC connection manager. If you set up and test your ODBC connection then you should be able to connect OK.	[reply] [d/l]