Accessing MS Access through the DBI ODBC

Hi all,

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
0
JavierMoreno
3/30/2005 11:41:48 PM
perl.dbi.users 11100 articles. 1 followers. Follow

4 Replies
877 Views

Similar Articles

[PageSpeed] 20

Hi Javier,

> 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
0
kevindotcar
3/31/2005 12:44:48 AM
Kevin,

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 =3D DBI->connect('dbi:ODBC:CLUCTR', '', '', { AutoCommit =3D> 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=3D-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) =3D @_;
=09
	# Print out error messages
	print "<p align=3D\"center\" class=3D\"Warning\">$error_string</p>\n";

	# Perform the below only if we have an optional SQL parameter
	if ($sql) {
	=09
		# Print the output from the SQL command
		print "<p align=3D\"left\" class=3D\"Normal\">$sql</p>\n";

		# Output the DBI error string which should be available
		print "<p align=3D\"left\" class=3D\"Normal\">$DBI::errstr</p>\n";

		# Before dying attempt a database disconnect just in case
		$::rc =3D $::dbh->disconnect or die "FATAL ERROR. Unable to disconnect =
from database.";
	}

	# Unable to continue. We know why so just die
	die;
}

Regards,

Javier Moreno

-----Original Message-----
From: Kevin Carothers [mailto:kevindotcar@gmail.com]
Sent: Wednesday, March 30, 2005 6:45 PM
To: Moreno, Javier
Cc: dbi-users@perl.org
Subject: Re: Accessing MS Access through the DBI ODBC


Hi Javier,

> 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.
>=20

 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 =3D DBI->connect("dbi:ODBC:ODBCName",'','');
[---]
   $sth =3D $dbh->prepare("select * from some_tablename");
   $sth->execute || die("Could not execute SQL statement ... maybe =
invalid?");
   while (@row_data =3D $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=20
permissions issue going on.   Post some code.

kevindot
0
JavierMoreno
3/31/2005 1:18:17 PM
It looks as though you are running CGI under a web server. The most common
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


On 31-Mar-2005 Moreno, Javier wrote:
> Kevin,
> 
> 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
> 
> -----Original Message-----
> From: Kevin Carothers [mailto:kevindotcar@gmail.com]
> Sent: Wednesday, March 30, 2005 6:45 PM
> To: Moreno, Javier
> Cc: dbi-users@perl.org
> Subject: Re: Accessing MS Access through the DBI ODBC
> 
> 
> Hi Javier,
> 
>> 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
0
martin
3/31/2005 1:23:44 PM
Hi Javier,

 [---] 
> 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
0
kevindotcar
3/31/2005 6:11:55 PM
Reply: