DBD::ODBC connection prob

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

I am having a problem connecting to a MS Access database using DBI and DBD::ODBC. I am able to connect when I set up a system DSN (via control panel Admin tools). Using this method I create a new System DSN called DYC using the driver "Microsoft Access Driver (*.mbd)" pointing to a file C:\dyc\dyc.mbd This works fine. My connect looks like this.

$dbh = DBI->connect('dbi:ODBC:DYC', '', '');
[download]

But I would like connect to the database without having to register the database file. The ODBC.pm file indicates that this is possible and offers a small snippet of code as an example...

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

The thing to the right of the "dbq=" is what is confusing me.
The doc refers to this as "Microsoft's UNC naming convention".
What would I put there to refer to the file c:\dyc\dyc.mbd?
I found another website that defines a UNC as
\\servername\sharename\path\filename
but does not offer any examples.
Can anyone help?

edited: Mon Jul 28 18:31:00 2003 by jeffa - code tags

Comment on DBD::ODBC connection prob Select or Download Code

Replies are listed 'Best First'.
Re: DBD::ODBC connection prob by Rich36 (Chaplain) on Mar 04, 2003 at 22:08 UTC
You should just be able to use `my $DSN = 'driver=Microsoft Access Driver(.mdb);dbq=C:/dyc/dyc.mdb';` [download] I had some problems with that when trying to use Win32::ODBC with a Access database. What I ended up doing was writing a file DSN on the fly, then cleaning it up on exit. That seemed to work a little better for me. It was something like this: `# Create the MS Access DSN my $dsncontents = <<"EODSN"; [ODBC] DRIVER=Microsoft Access Driver (.mdb) UID=admin UserCommitSync=Yes Threads=3 SafeTransactions=0 PageTimeout=5 MaxScanRows=8 MaxBufferSize=2048 FIL=MS Access DriverId=25 DefaultDir=$datadir DBQ=$file EODSN open(DSN, ">$datadir/Report.dsn") or die "Couldn't create new DSN!\n"; print DSN $dsncontents; close(DSN); $accessdsn = "FILEDSN=$datadir/Report.dsn";` [download] Where I had previously defined the access file name (`$file` ), data directory (`$datadir` ) and then used `$accessdsn` as the parameter for the call. All I did was create a working file DSN for the database, then copy the contents into my application, changing certain elements to variables. In your case, if the datadir and file name are constant, you could probably just have all the values hardcoded or just put a file DSN in with the Access database. «Rich36»	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: DBD::ODBC connection prob
by Rich36 (Chaplain) on Mar 04, 2003 at 22:08 UTC

You should just be able to use

my $DSN = 'driver=Microsoft Access Driver(*.mdb);dbq=C:/dyc/dyc.mdb';
[download]

I had some problems with that when trying to use Win32::ODBC with a Access database. What I ended up doing was writing a file DSN on the fly, then cleaning it up on exit. That seemed to work a little better for me. It was something like this:

# Create the MS Access DSN 
    my $dsncontents = <<"EODSN";
[ODBC]
DRIVER=Microsoft Access Driver (*.mdb)
UID=admin
UserCommitSync=Yes
Threads=3
SafeTransactions=0
PageTimeout=5
MaxScanRows=8
MaxBufferSize=2048
FIL=MS Access
DriverId=25
DefaultDir=$datadir
DBQ=$file
EODSN

open(DSN, ">$datadir/Report.dsn") or die "Couldn't create new DSN!\n";
print DSN $dsncontents;
close(DSN);    
    
$accessdsn = "FILEDSN=$datadir/Report.dsn";
[download]

Where I had previously defined the access file name ($file ), data directory ($datadir ) and then used $accessdsn as the parameter for the call. All I did was create a working file DSN for the database, then copy the contents into my application, changing certain elements to variables. In your case, if the datadir and file name are constant, you could probably just have all the values hardcoded or just put a file DSN in with the Access database.

«Rich36»

[reply]
[d/l]
[select]