MS Access and DBI

[Tript99]

Hello,
I am having a ton of trouble trying to connect to an access database via ODBC with a perl job. I have an MS access database on my desktop that I have set up a System Datasource name of "Audit" with the path to the database. Then I used some code I found on the net to perform a simple SQL query:

#Windows-based Perl/DBI/MS Access example

use DBI;

#open connection to Access database
$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\Windows\Desktop\AuditTrail.mdb');

#prepare and execute SQL statement
$sqlstatement="SELECT PRODNO FROM tblCMFBridges";
$sth = $dbh->prepare($sqlstatement);
$sth->execute ||
die "Could not execute SQL statement ... maybe invalid?";

#output database results
while (@row=$sth->fetchrow_array)
{ print "@row\n" }



I continually get the following error:

C:\Perl2exe>perl audit2.txt
Can't locate loadable object for module DBI in @INC (@INC contains: C:/perl/lib
C:/perl/site/lib .) at C:/perl/lib/DBI.pm line 255
BEGIN failed--compilation aborted at C:/perl/lib/DBI.pm line 255.
Compilation failed in require at audit2.txt line 3.
BEGIN failed--compilation aborted at audit2.txt line 3.

I have loaded DBI.pm as well as ODBC.pm.

Any ideas???
Thanks for your help.
Tript.

 

[fishiface]

DBI is a generic front end that requires drivers to function. The drivers are known as DBD::*, eg DBD::Informix. You may have a problem loading the correct DBD driver (DBD::ODBC) but my gut feel is that your DBI is not installed correctly: it looks as if you have DBI.pm itself but not all of the associated files.

On my system, these include[tt]
/usr/lib/perl5/auto/DBI/DBIXS.h
/usr/lib/perl5/auto/DBI/dbi_sql.h
/usr/lib/perl5/auto/DBI/dbipport.h
/usr/lib/perl5/auto/DBI/dbd_xsh.h
/usr/lib/perl5/auto/DBI/Driver.xst
/usr/lib/perl5/auto/DBI/DBI.so
/usr/lib/perl5/auto/DBI/DBI.bs
/usr/lib/perl5/DBI.pm
/usr/lib/perl5/DBI/Changes.pm
/usr/lib/perl5/DBI/ProxyServer.pm
/usr/lib/perl5/DBI/Format.pm
/usr/lib/perl5/DBI/Shell.pm
/usr/lib/perl5/DBI/FAQ.pm
/usr/lib/perl5/DBI/W32ODBC.pm
/usr/lib/perl5/DBI/DBD.pm
/usr/bin/dbiproxy
/usr/bin/dbish[/tt]
as well as the documentation, etc. Are you sure that your copy of DBI is correctly installed?

Yours,


fish

"As soon as we started programming, we found to our surprise that it wasn't as easy to get programs right as we had thought. Debugging had to be discovered. I can remember the exact instant when I realized that a large part of my life from then on was going to be spent in finding mistakes in my own programs."
--Maurice Wilkes

 

[PaulTEG]

How did you install DBD? CPAN, PPM or IPM?

--Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[Tript99]

Hi Paul,
I used PPM.

 

[PaulTEG]

Try installing again using PPM, and if you're still getting the same issue, try installing using CPAN

c:\perl\bin>perl -MCPAN -e shell

Check Barbie's link in the FAQ section

http://www.tek-tips.com/faqs.cfm?spid=219&sfid=1687

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[redjoy]

#open connection to Access database
$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\Windows\Desktop\AuditTrail.mdb');
[\code]
Try changeing this line to
[code]
#open connection to Access database
$dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=C:\\Windows\\Desktop\\AuditTrail.mdb');
[\code]
double backslashes worked for me.