Using MS Access via DBI and DBD::ODBC

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

Dear Monks,

do I have to connect to the database in a different way when a .mdw-file exists?

I try to access a database and it works fine with databases that have no user-password-combination.

This is the code I use:

#! /usr/bin/perl

use strict;
use warnings;
use DBI;

my $user = 'username';
my $pass = 'password';

my $dsn = 'driver=Microsoft Access-Treiber (*.mdb); dbq=db1.mdb';
my $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$pass) or die $DBI::errst
+r;

[...]
[download]

I just get the message, that I do not have the right to work with the object...

Comment on Using MS Access via DBI and DBD::ODBC Download Code

Replies are listed 'Best First'.
Re: Using MS Access via DBI and DBD::ODBC by davidrw (Prior) on Jun 13, 2005 at 12:34 UTC
The obvious is, do you have the right? :) What (windows) user is the script running under and is it able to manually open the db1.mdb? Maybe there's a problem w/ the username/password or use thereof: If you manually open db1.mdb and remove the username/password, and also blank the values in your script, does it work then (if not, what's the error)?	[reply]
Re^2: Using MS Access via DBI and DBD::ODBC by reneeb (Chaplain) on Jun 13, 2005 at 12:42 UTC
If I open the database manually (with user/password) I can work with the database.	[reply]
Re: Using MS Access via DBI and DBD::ODBC by JupiterCrash (Monk) on Jun 13, 2005 at 14:10 UTC
Are your running the script from the web? If so, the anonymous IIS user (IUSR_MACHINENAME) needs to have permission to the MS Access file. Matt	[reply]
Re^2: Using MS Access via DBI and DBD::ODBC by reneeb (Chaplain) on Jun 13, 2005 at 20:21 UTC
No, it's no web-application. I've written the module on my laptop and made an executable with PAR and run it on an other PC.	[reply]
Re: Using MS Access via DBI and DBD::ODBC by runrig (Abbot) on Jun 13, 2005 at 20:26 UTC
What if you create a dsn through Control Panel/ODBC Data Sources? The user/password can be configured there, so that your program does not need it.	[reply]
Re^2: Using MS Access via DBI and DBD::ODBC by reneeb (Chaplain) on Jul 15, 2005 at 14:13 UTC
Sorry for that late answer, but I was ill the last three weeks. I've solved the problem! I want to post the solution: You have to change the $dsn-String. The path to the .mdw-file has to be added with the key "SystemDB": `#! /usr/bin/perl use strict; use warnings; use DBI; my $user = 'username'; my $pass = 'password'; my $dsn = 'driver=Microsoft Access-Treiber (*.mdb); '; $dsn .= 'dbq=db1.mdb; SystemDB=c:\path\to\file.mdw'; # to add the SystemDB-key is the solution! my $dbh = DBI->connect("DBI:ODBC:$dsn",$user,$pass) or die $DBI::errstr; [...]` [download]	[reply] [d/l]