Accessing MS Access through the DBI ODBC
Javier Moreno
I am trying to access an MS Access database via perl DBI and I am unable to. I checked the security and there is only one 'Admin' user with no password and I use that in my connection string. However, I cannot connect. Please help.
Regards,
Javier Moreno
Kevin Carothers
> I am trying to access an MS Access database via perl DBI and I am unable to. I checked the security and there is only one 'Admin' user with no password and I use that in my connection string. However, I cannot connect. Please help.
>
1. Create an ODBC connection via %SystemRoot%\system32\odbcad32.exe
Call the database connect name "ODBCName"
2. In your Perl code add:
[---]
use DBI;
[---]
$dbh = DBI->connect("dbi:ODBC:ODBCName",'','');
[---]
$sth = $dbh->prepare("select * from some_tablename");
$sth->execute || die("Could not execute SQL statement ... maybe invalid?");
while (@row_data = $sth->fetchrow_array()) {
print $row_data[0]; ### print first column of table 'some_tablename'
}
...That's really about it... everything else is on an as-needed basis.
If the database is on a network drive, maybe there is some sort of
permissions issue going on. Post some code.
kevindot
Javier Moreno
I tried removing the user and the pwd from the ODBC connect string but I am still getting 'Unable to connect to database'. Here is my code
# Include CPAN modules for DB communication
use DBI;
use DBD::ODBC;
# Use these pragmas to code correctly
use strict;
# Connect to the database, return error message if something went wrong
$::dbh = DBI->connect('dbi:ODBC:CLUCTR', '', '', { AutoCommit => 0 }) or
&Error("Unable to connect to database.");
And on the log I get:
[Thu Mar 31 07:12:56 2005] [error] [client 151.110.117.153] DBI connect('CLUCTR','',...) failed: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/CLUCTR.pl line 53\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl
[Thu Mar 31 07:12:56 2005] [error] [client 151.110.117.153] Died at Functions.pl line 139.\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl
Line 139 is the die on my catch-all Error function
# Useage: Error (<any_error_text>, <optional_sql_code_to_display>)
# This routine is a catch-all error routine. It will take in a reason for failure as a string and append the DBI
# returned error message. Then it will simply display it and die since there is no reason to go further and to ease
# research of error.
sub Error {
my ($error_string, $sql) = @_;
# Print out error messages
print "<p align=\"center\" class=\"Warning\">$error_string</p>\n";
# Perform the below only if we have an optional SQL parameter
if ($sql) {
# Print the output from the SQL command
print "<p align=\"left\" class=\"Normal\">$sql</p>\n";
# Output the DBI error string which should be available
print "<p align=\"left\" class=\"Normal\">$DBI::errstr</p>\n";
# Before dying attempt a database disconnect just in case
$::rc = $::dbh->disconnect or die "FATAL ERROR. Unable to disconnect from database.";
}
# Unable to continue. We know why so just die
die;
}
Regards,
Javier Moreno
Martin J. Evans
reason for this issue is the mdb is on a mapped drive where the mapped driver
was mapped by a different user to the one the web server is running as.
However, there are loads of other reasons for this - search msdn.microsoft.com
which lists all the possibilities.
This URL in our FAQ lists many of them:
http://www.easysoft.com/products/9999/faq_answer.phtml?ID=686&product=2002
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
># failure as a string and append the DBI
># returned error message. Then it will simply display it and die since there
># is no reason to go further and to ease
Amonotod
> Date: 2005/03/30 Wed PM 06:44:48 CST
> 1. Create an ODBC connection via %SystemRoot%\system32\odbcad32.exe
> Call the database connect name "ODBCName"
>
> 2. In your Perl code add:
> [---]
> use DBI;
> [---]
> $dbh = DBI->connect("dbi:ODBC:ODBCName",'','');
> [---]
Just my opinion, but I don't think this is good advice. For one, it creates
extra administration on the system, by forcing the user/programmer
/sysadmin/webadmin to create an ODBC DSN setting for every new setup.
For two, DBI and ODBC both support dynamic DSNs, so why not use them?
Here is a connect string that works for Access databases:
my $dbh = DBI->connect( "dbi:ADO::Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$path_to_mdb_file" ,'admin', {RaiseError => 1, AutoCommit => 1} );
And here's a little extra, showing how to build connections for Access
(via ADO), MSSQL (via ODBC), Sybase, and Oracle...
my %dbDrivers = ("SYBASE" => "Sybase",
"ORACLE" => "Oracle",
"MSSQL" => "ODBC",
"ACCESS9" => "ADO");
my $ConnStr = buildConn();
my $dbh;
if ((uc($arg_db_type) eq "ACCESS2K")&&(uc($arg_db_type) eq "ACCESS97")) {
$dbh = DBI->connect( $ConnStr, 'admin', {RaiseError => 1, AutoCommit => 1} );
} elsif (uc($arg_db_type) eq "SYBASE") {
$dbh = DBI->connect( $ConnStr.";database=$arg_use_db", $arg_sysuser, $arg_syspwd, {RaiseError => 1, AutoCommit => 1} );
} elsif (uc($arg_db_type) eq "ORACLE") {
$dbh = DBI->connect( $ConnStr, $arg_sysuser, $arg_syspwd, {RaiseError => 1, AutoCommit => 1} );
}
# Build Connection String
sub buildConn {
my $retval = "dbi:". $dbDrivers{$arg_db_type};
if (uc($arg_db_type) eq "ORACLE") {
$retval .= ":host=". $arg_connection .";sid=". $arg_use_db;
} elsif (uc($arg_db_type) eq "SYBASE") {
$retval .= ":server=". $arg_connection;
}elsif (uc($arg_db_type) eq "MSSQL") {
$retval .= ':driver={SQL Server};Server='. $arg_connection .';database='. $arg_use_db .';';
}elsif ((uc($arg_db_type) eq "ACCESS97") or (uc($arg_db_type) eq "ACCESS2K")) {
$arg_syspwd = ''; # Need to set the password to NULL for Access....
$retval .= ":Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=". $arg_use_db ."";
}
return $retval;
}
> kevindot
HTH,
amonotod
--
`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|
Kevin Carothers
[---]
> And on the log I get:
> [Thu Mar 31 07:12:56 2005] [error] [client 151.110.117.153] DBI connect('CLUCTR','',...) failed: [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data. (SQL-HY000)(DBD: db_login/SQLConnect err=-1) at C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/CLUCTR.pl line 53\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl
> [Thu Mar 31 07:12:56 2005] [error] [client 151.110.117.153] Died at Functions.pl line 139.\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl
[--]
From the ODBC Data Source Administrator, can you see a system DSN
named "CLUCTR" ? And if so, does it use "Microsoft Access Driver"?
If the above is true, when you double-click the data source, is the
"Database:" file pointing to the right MDB file? And if this is
true, when you click on "Advanced" options, Are the user name and
password correct? If the database does not have a password, leave
both blank, and see if that helps.
If anyone else has any ideas, chime in.
Later,
kevindot
Jeff Urlwin
> Just my opinion, but I don't think this is good advice. For
> one, it creates extra administration on the system, by
> forcing the user/programmer /sysadmin/webadmin to create an
> ODBC DSN setting for every new setup. For two, DBI and ODBC
> both support dynamic DSNs, so why not use them?
If you have separate development, test and production environments, it's sometimes easier
to use DSNs. That way, when you migrate from dev to test, for example, you automagically
get the test environment connection information. That's one reason why I like DSNs over
putting them in the code.
Of course, TMTOWTDI...
Jeff