[Paul H]

I have been looking for information on how to read mdb files from Perl. I can't see what I am doing wrong, but I am not able to connect to any mdb file. My environment is ActivePerl on Vista Business.

Here is my code:

use strict;
use Data::Dumper;
use DBI;

#open connection to Access database
my $dbh = DBI->connect('dbi:ODBC:driver=microsoft access driver (*.mdb);dbq=c:\Users\paul.hodor\Documents\testdb.m db')
or die "Can't connect to Oracle database: $DBI::errstr\n";;

print Dumper $dbh;

#prepare and execute SQL statement
my $sqlstatement="SELECT * FROM nametable";
my $sth = $dbh->prepare($sqlstatement) or die "Can't prepare SQL statement: $DBI::errstr\n";
my $sth->execute or die "Can't execute SQL statement: $DBI::errstr\n";

And the output:

$VAR1 = bless( {}, 'DBI::db' );
Can't call method "execute" on an undefined value at mdb_extract.pl line 14.

The file specification appears to be correct. If I change the file name to something non-existent, I get the following error:

DBI connect('driver=microsoft access driver (*.mdb);dbq=c:\Users\paul.hodor\Documents\testdb1. mdb','
',...) failed: [Microsoft][ODBC Microsoft Access Driver] Could not find file '(unknown)'. (SQL-HY000
) at mdb_extract.pl line 6
Can't connect to Oracle database: [Microsoft][ODBC Microsoft Access Driver] Could not find file '(un
known)'. (SQL-HY000)

I am also able to convert the file to csv using the DataConversionTool.com MDB Exporter. I am puzzled that it does not work from Perl. Any help will be appreciated.

[kshtoperl]

So u are wrong at $sth->execute!!!!!!!!!!!!!!

Use the follwoing(see the "()" after execute)
$sth->execute();

it is not able to identify ur execute.

[Paul H]

I wish it had been that simple... Tried it, but the parentheses did not change anything

[sco08y]

Can you use ADO instead of ODBC? (You'll need the DBD::ADO driver from package manager) The JET engine works most closely with DAO, but ADO is a close second, and you can actually get drivers for ADO. This requires that msjetoledb40.dll be in your system folder. (Has that changed to something other than windows/system32 on Vista? I've never upgraded from XP...)

My code to connect to an mdb file and dump all table info is:

use DBI;
use strict;

my($user, $pass) = ();
my $dbh = DBI->connect("dbi:ADO:Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/foo/bar/database.mdb", $user, $pass, { RaiseError=>1, PrintError=>1 });

$dbh->table_info->dump_results(60); # Use 60 character lines instead of 35

$dbh->disconnect();

Also, what version of ActivePerl are you using, and what version of DBI?

So u are wrong at $sth->execute!!!!!!!!!!!!!!

p3rl d0esnt need teh ()s aft3r teh m3th0d kall w no params! LOLOL!!!! p3rl w00d throw err0r @ c0mpi1e time 4 a synt4x err0r!!!!!1!11!! u r teh sux0rz n00b!!!!11!!!!!

[stevkato]

Your post is a little confusing. You're using the MS Access driver to query an Oracle DB? If you're trying to connect to an Access db, then you might want to check that you have DBD::ODBC installed. Here's what I had to do to get perl to connect to an Access db:
installed DBI perl module (looks like you've done that)
installed DBD::ODBC - I had to set the ODBCHOME environment variable to point to the location of the Access driver before the install would work.
setup an odbc connection: start->admin tools->odbc->User DSN->Add…->Microsoft Access Driver (*.mdb) (pick the ds name and select the database you want to connect up) then go to Select-> and select the path to the database. e.g. x:\dbs\testdb\tdb.mdb NOTE: if you are allowing access via other users, use System DSN.