How do I connect to MS Access database

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

Hey Monks,

I have gone through many of the messages posted here related to MS access accessing.
I am trying to use DBD::ODBC, for some reason I am not able to connect.

my $DSN = "driver=Microsoft Access Driver(*.mdb);dbq=Z:\\\\temp\\Kerne
+lLog.mdb;"
$dbh = DBI->connect("dbi:ODBC:$DSN", '','')  or die "$DBI::errstr\n";
[download]

It throws an error like

DBI connect.... failed: [Microsoft][ODBC Driver Manager] Data source n
+ame not found and no default driver specified (SQL-IM002)(DBD: db_log
+in/SQLConnect err=-1) at Logparse/ParsetoDB.pm line 67
[download]

I checked the ODBC datasource and I do see the driver name specified by me.
What am I doing wrong here?
Thanks!

Comment on How do I connect to MS Access database Select or Download Code

Replies are listed 'Best First'.
Re: How do I connect to MS Access database by kennethk (Abbot) on Apr 29, 2009 at 20:57 UTC
There's a typo in your $DSN assignment where you swap ; and " - I can only assume that means you typed rather than copy/pasted your handle. Try this and see how it works. Note that to avoid escaping errors, I set a file name separately from my driver specification. `my $filename = 'Z:\\temp\KernelLog.mdb'; my $dsn = "dbi:ODBC:driver=Microsoft Access Driver (.mdb); dbq=$filen +ame"; my $dbh = DBI->connect( $dsn, '', '', { PrintError => 1, RaiseError => 0, AutoCommit => 0, } ) or die "DB connect failed $DBI::errstr.";` [download] As a coincidence, I am just now migrating an Access database to Oracle. An Access database with non-alphanumeric table and column names. Oy. Update:* Your issue appears to be that your driver specification requires a space between Driver and (*.mdb).	[reply] [d/l]

Replies are listed 'Best First'.

Re: How do I connect to MS Access database
by kennethk (Abbot) on Apr 29, 2009 at 20:57 UTC

There's a typo in your $DSN assignment where you swap ; and " - I can only assume that means you typed rather than copy/pasted your handle. Try this and see how it works. Note that to avoid escaping errors, I set a file name separately from my driver specification.

my $filename = 'Z:\\temp\KernelLog.mdb';
my $dsn = "dbi:ODBC:driver=Microsoft Access Driver (*.mdb); dbq=$filen
+ame";
my $dbh = DBI->connect(    $dsn, '', '',
                        {
                            PrintError => 1,
                            RaiseError => 0,
                            AutoCommit => 0,
                        }
                    )
    or die "DB connect failed $DBI::errstr.";
[download]

As a coincidence, I am just now migrating an Access database to Oracle. An Access database with non-alphanumeric table and column names. Oy.

Update: Your issue appears to be that your driver specification requires a space between Driver and (*.mdb).

[reply]
[d/l]