Accessing MS Access

[Javier Moreno]

Hi all,

This is probably a more DB oriented question, but it came out of the DBI. I posted a question and have tried several solutions on how to open up an MS Access DB from the DBI::ODBC but so far have been unable to. What I had to end up doing was create a DTS that runs every 5 minutes which copies the table I need from Access and populates a SQL Server table. This causes a delay on my web application such that whenever my page overlaps while the DTS is running I get no data. So I was wondering if there is a better way to get information from Access so I can see it on my CGI with DBI.

Regards,

Javier Moreno

[Amonotod]

Date: 2005/04/08 Fri PM 03:44:40 CDT

<snip>

I posted a question and have tried several solutions on how to open
up an MS Access DB from the DBI::ODBC but so far have been unable to.

<snip>

I was wondering if there is a better way to get information
from Access so I can see it on my CGI with DBI.

<snip>

Well, I've posted several times (the last time was in a reply to a
thread that you started) that you should use ADO to connect to your
Access database, and I gave you connection examples. So, I guess the
answer is yes, there may indeed be a better way to connect to your
Access database than with DBD::ODBC. If not better, at least different.

http://www.nntp.perl.org/group/perl.dbi.users/26145

Regards,
Javier Moreno

HTH,
amonotod

--

`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|

[Javier Moreno]

Ok. I have tried ADO as well with no luck. Please help!

<snip>

# Include CPAN modules for DB communication
use DBI;
#use DBD::ODBC;
use DBD::ADO;
#use Win32::OLE;

# Use these pragmas to code correctly
use strict;

# Include custom functions
require "Functions.pl";

# Debug
$::DEBUG=1;

# Connect to the database, return error message if something went wrong
my ($path_to_mdb_file) = '\\slpmxwtstandclu\testdata$\Data_2.mdb';
my ($dsn)= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=$path_to_mdb_file";
&Debug("About to connect to $dsn");
#$::dbh = DBI->connect( "dbi:ADO::Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;Data Source=$path_to_mdb_file" ,'admin', { AutoCommit => 1} ) or
$::dbh = DBI->connect("dbi:ADO:$dsn", 'Admin', '', { AutoCommit => 1} ) or
&Error("Unable to connect to database.");

<endsnip>

With the first connection string I got:

[Mon Apr 11 14:23:42 2005] [error] [client 151.110.117.153] Usage: $class->connect([$dsn [,$user [,$passwd [,\\%attr]]]]) at C:/oracle/ora92/Apache/Apache/ITTWeb/cgi-bin/CLUCTR.pl line 63\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl

while with the second I get:

[Mon Apr 11 14:29:57 2005] [error] [client 151.110.117.153] dbih_getcom given an undefined handle (perhaps returned from a previous call which failed) at C:/Perl/site/lib/DBI.pm line 598.\r, referer: http://slpmxwmorenojav/ITTWeb/cgi-bin/Welcome.pl

Regards,

Javier Moreno

-----Original Message-----
From: amonotod [mailto:***@charter.net]
Sent: Friday, April 08, 2005 8:07 PM
To: Moreno, Javier; dbi-***@perl.org
Subject: Re: Accessing MS Access

Date: 2005/04/08 Fri PM 03:44:40 CDT

<snip>

I posted a question and have tried several solutions on how to open
up an MS Access DB from the DBI::ODBC but so far have been unable to.

<snip>

I was wondering if there is a better way to get information
from Access so I can see it on my CGI with DBI.

<snip>

Well, I've posted several times (the last time was in a reply to a
thread that you started) that you should use ADO to connect to your
Access database, and I gave you connection examples. So, I guess the
answer is yes, there may indeed be a better way to connect to your
Access database than with DBD::ODBC. If not better, at least different.

http://www.nntp.perl.org/group/perl.dbi.users/26145

Regards,
Javier Moreno

HTH,
amonotod

--

`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|

[Kevin Carothers]

Post by Javier Moreno
Ok. I have tried ADO as well with no luck. Please help!
<snip>
# Include CPAN modules for DB communication
use DBI;
#use DBD::ODBC;
use DBD::ADO;
#use Win32::OLE;
# Use these pragmas to code correctly
use strict;
# Include custom functions
require "Functions.pl";
# Debug
$::DEBUG=1;
# Connect to the database, return error message if something went wrong
my ($path_to_mdb_file) = '\\slpmxwtstandclu\testdata$\Data_2.mdb';

Aha!
You are attempting to connect to a database on what looks like a network share.
My guess is this is probably not a Perl issue, but rather a network
authentication issue-
Try to create an empty text file on "'\\slpmxwtstandclu" from the
same account you're running your script from... If you can, then my
guess is there's something funny going on in the Access DB.

But if you can't, then make sure the network account from which you
physically run the perl script has read/write permissions for the
network resource you are using.... and if you schedule this perl
script to run at a specific time, you have to make sure the
definition of the scheduled task ALSO has the network authentication
parameters (user name / passwd) correct.

HTH-
kc

[Amonotod]

Date: 2005/04/11 Mon PM 03:06:59 CDT

Post by Javier Moreno
Ok. I have tried ADO as well with no luck. Please help!

<snip>

Post by Javier Moreno
my ($path_to_mdb_file) = '\\slpmxwtstandclu\testdata$\Data_2.mdb';

Aha!
You are attempting to connect to a database on what looks like a network share.
My guess is this is probably not a Perl issue, but rather a network
authentication issue-

<snip>

But if you can't, then make sure the network account from which you
physically run the perl script has read/write permissions for the
network resource you are using...

Another way to test that theory, is copying your .mdb file over to a
local path on the webserver, like \\webserver\c$\temp, then changing
$path_to_mdb_file = "c:/temp/Data_2.mdb"

In case that is the issue, and you're running IIS, here you go:
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q207671

HTH,
amonotod


--

`\|||/ amonotod@ | sun|perl|windows
(@@) charter.net | sysadmin|dba
ooO_(_)_Ooo____________________________________
_____|_____|_____|_____|_____|_____|_____|_____|