Connect perl to ms access

Question

I am tring to retrive data from a table in access. The code is

#!/usr/bin/perl  
use strict;  
use warnings;  
use DBI;  
my $DBFile  = qw(C:test\INSTRUCTIONS.mdb );   
my $dbh = DBI->connect("dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=$DBFile",'','') or die("cannot connect to DB");  
my $SQLquery = "select * FROM IndemDate";  
$dbh->Execute($SQLquery);  

This is the error i recieve

DBI connect('driver=microsoft access driver (*.mdb);dbq=C:test\INSTRUCTIONS.mdb','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:/Test/connectaccess.pl line 9.
cannot connect to DB at C:/Test/connectaccess.pl line 9.

can someone help me rectify my error. Is there any driver I've missed to install.

Jameyjami

try using my $DBFile = 'C:/test/INSTRUCTIONS.mdb'; die "troubles with path of the file" if not -e $DBFile; maybe there are troubles with passing that argument.. you can use that lines to check if it is like i wrote. – Temperament

Have you tried setting up an ODBC connection through the control panel to make sure that side of things is OK? – Truckage

@FilippoLauria it did not work that way either – Jameyjami

@RichardHuxton I've performed SQL connection successfully, which proves ODBC connection is ok rite.. – Jameyjami

Check for a typo in the driver-name. Make sure it matches exactly what's listed in the Data Sources (ODBC) control panel. Oh, you don't want qw() on the filename setup, you probably meant q(). – Truckage

@RichardHuxton I still recieve the same error if I relpace qw() with q() – Jameyjami

Post your latest code. Make sure you put a slash after C: – Amr

Is your Perl script running as a 64-bit process? – Nisbet

How did you finally get it working? – Adduction

Answer 1

As indicated in the comments to the question, the Perl script was originally running as a 64-bit process. Therefore the older Microsoft "Jet" ODBC driver

Microsoft Access Driver (*.mdb)

was not available. Only 32-bit processes can use the older Jet driver. If you must run your Perl script as a 64-bit process then you will have to download and install the 64-bit version of the newer Microsoft Access Database Engine (a.k.a. "ACE") from here and then use the driver name

Microsoft Access Driver (*.mdb, *.accdb)

Or, you could run your Perl script as a 32-bit process and use the older Jet driver.

Edit re: comment

Since you have 32-bit Access 2007 installed you already have a 32-bit version of the ACE driver on the machine. That effectively eliminates the option to install the 64-bit version of the ACE driver because the 64-bit ACE installer will abort if it finds 32-bit Office components on the machine. (There is apparently a way to force the second install but it is reported to break Office in some circumstances and is definitely not supported.)

So your options can be revised to:

1. Run the Perl script as a 32-bit process, or

2. Remove 32-bit Access 2007 and install a 64-bit version of Access, then run the Perl script as a 64-bit process using the 64-bit ACE driver.

Nisbet

but i am using ms access 2007 – Jameyjami

@Jameyjami Is it 32-bit Access 2007? (I honestly don't remember if there was a 64-bit version of Access 2007.) – Nisbet

ms access 32-bit is what i am running on a 64 bit OS – Jameyjami

yes but if I execute the program it returns the error... what is the error in my program – Jameyjami

@Jameyjami Are you saying that you are now running the Perl script as a 32-bit process and you are still getting an error? – Nisbet

yes.... that is the reason why I have menntioned if i have missed installing any driver – Jameyjami

Answer 2

Try to use Jet:

#!/usr/bin/perl -w
use strict;
use warnings;

use Win32::OLE;

my $DBFile  = qw( C:\test\INSTRUCTIONS.mdb );

# choose the appropriate versione of Jet for your system
my $Jet = Win32::OLE->CreateObject('DAO.DBEngine.36') or die "Can't create Jet database engine.";

my  $DB = $Jet->OpenDatabase( $DBFile );
my $SQLquery = "select * FROM IndemDate";
$DB->Execute($SQLquery, 128); #128=DBFailOnError

[source: here, also have a look here]

Temperament

Win32::OLE(0.1709) error 0x80040154: "Class not registered" at C:/test/connectaccess.pl line 10. Can't create Jet database engine. at C:/test/connectaccess.pl line 10. This is the error i get – Jameyjami

Answer 3

For a script running as a 32-bit process (in my case, 32-bit Strawberry Perl), the following code works for me:

#!perl  
use strict;  
use warnings;  
use DBI;  
print 'bits: ' . (8 * (length pack 'P', -1)) . "\n\n";
my $DBFile = q(C:\Users\Public\mdbTest.mdb);   
my $dbh = DBI->connect("dbi:ODBC:Driver={Microsoft Access Driver (*.mdb)};DBQ=$DBFile",'','') or die("cannot connect to DB");  
my $SQLquery = "SELECT * FROM Members";  
my $sth = $dbh->prepare($SQLquery);
my $rc = $sth->execute;
while (my $href = $sth->fetchrow_hashref) {
    print "memberID: " . $$href{"memberID"} . "\n";
    print "memberName: " . $$href{"memberName"} . "\n";
    print "\n";
}

Nisbet

no i still get the error DBI connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\INSTRUCTIONS.mdb','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:/test/connectaccess.pl line 6. cannot connect to DB at C:/test/connectaccess.pl line 6. – Jameyjami

I am using active perl – Jameyjami

@Jameyjami Try adding the statement print 'bits: ' . (8 * (length pack 'P', -1)) . "\n\n"; right near the beginning of your script. (See revised code, above.) What does it print? – Nisbet

DBI connect('Driver={Microsoft Access Driver (*.mdb)};DBQ=C:\test\INSTRUCTIONS.mdb','',...) failed: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified (SQL-IM002) at C:/test/connectaccess.pl line 7. cannot connect to DB at C:/test/connectaccess.pl line 7. – Jameyjami

@Jameyjami Once you added the extra statement did the script not print anything before the error message? – Nisbet

@Jameyjami ...so it appears that your script is still running as a 64-bit process. See my other answer. – Nisbet

so how to run the program as 32-bit process – Jameyjami

@Jameyjami You may need to install a 32-bit version of the Perl interpreter and then use it to execute your script. It would probably be prudent to do a bit of searching to see if ActivePerl has a way to do that with the setup you have now. (Maybe 64-bit ActivePerl installs 32-bit components, too... I don't know.) – Nisbet