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

Dear Fellow Monks,

I am stumped regarding an issue with the WIN32:ODBC module and a Micros~1 Access Database.

Comming from a completely Un*x-y background, I have found a need to use the WIN32:ODBC driver to connect to a Windows Access database via Perl CGI. I have done this before with an already existing Access database. But now I need to use a DNS-less connection, as well as an Access database I made myself. I created a database using Access and uploaded it to my server. I then used the code below to connect to the database. I didn't get a connection error. However, when I tried to call any methods on my "$db" object, I get errors. What do I have to do to successfully connect? Do I have to save my Access database in some special way? Does any Monk know where I can go to read more about this, I can't find a good reference anywhere!

My code looks like this: 
# Load necessary modules
use Win32::ODBC;  
use CGI;
use CGI::Carp qw(fatalsToBrowser);

# DNS related Variables...
$username = "foo";
$password = "bar";
$database_path = "name_of_my_database.mdb";        

#create database object called $db...         
$db = new Win32::ODBC("DNS=$database_path;UID=$username;PWD=$password"
+);    

# These lines do not give me an error...
# Doesn't this mean I have a proper connection?
if (undef $db){
    Win32::ODBC::DumpError();
}

# This line gives me an error which says something
# like Can't call method "Connection" on undefined
# value...
$con_number = $db->Connection;

[download]
Thanks Monks!

Replies are listed 'Best First'.
Re: WIN32:ODBC Perl Connection to Micros~1 Access Database
by simon.proctor (Vicar) on Jan 05, 2002 at 06:33 UTC
    If you still have problems, try making your path to the database file a full path, ie H:/data.mdb rather than data.mdb (say). Rather trivial but it has caught me out sometimes on my NT4 machine at work.

    You could also try a different connection string: 

    Driver={Microsoft Access Driver (*.mdb)}; DBQ=database.mdb

    [download]


    I would also recommend taking a look at the OLE based access to an Access database. Here you can have access to the ADO stuff directly within Perl. An example bit of code for inserting and retrieving the last insert ID can be found in: Retrieving the last insert ID with Access.

    However, if you do try the OLE stuff then you really do need to learn how ASP does it as it works in almost exactly the same way. I know thats almost swearing here (oops :P) but the docs for recordsets and connection objects in ASP are quite complete so you should be able to find what you need.

    Hope that helps.

    Update: Added connection string
[reply]
[d/l]
Re: WIN32:ODBC Perl Connection to Micros~1 Access Database
by chromatic (Archbishop) on Jan 14, 2002 at 03:55 UTC
    Replace undef with defined (and probably if with unless, or at least negate the defined.) You're whacking the Win32::ODBC object accidentally -- so it's undefined when you try to call anything on it.
[reply]
Re: WIN32:ODBC Perl Connection to Micros~1 Access Database
by cheshirecat (Sexton) on Jan 05, 2002 at 05:36 UTC
    Try changing DNS=$database_path to
    DSN=$database_path

    DSN stands for Data Source Name and NOT Domain Name Service

    You could also look at roth.net as Dave Roth was responsible for this module.

    Cheers
    CheshireCat :)

[reply]

      Good eye Cheshire, but that actually was my own typo, it doesn't work even with "DSN". Actually, this is strange, I can put anything there and it still doesn't crash! I would expect the constructor to throw an exception if it gets messed up connection data... I used "SuperTRONICON2001" as my user name (that's not really my user name) and it still didn't give me any errors until I started using other object methods!

[reply]

        Oops, I didn't log in... that was me, Mr. Don't!

[reply]
        The way I do this is to create a DSN using the ODBC Control panel in Windows. I usually create a system DSN, then I use the name I give it as my name in the connection string.

        This way the dsn can point at any database I want (including excel or access or MS SQL)

        Cheers

        Cheshire Cat

[reply]

Back to Seekers of Perl Wisdom