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

I have a mdb access data file. I want to open the access database and parse through it (in code). I have no other information on the file except it's an access database and that's it - given to me from someone else. How can I open the access database in perl (a simple example would be nice to get started - open a connection for example)? I've seen some examples on the web already, one is where you need to specify a dsn (using DBI). How can I determine what is dsns are available to me (on my system) for this parameter? Setting up the connection is my first hurdle, if I can determine how/what I need to do that for starters would be great. Can anyone suggest an approach to take to make a connection to an access database and how to figure out what I need to pass as parameters? The mdb is C:/Temp/AccessDB/somename.mdb.

Replies are listed 'Best First'.
Re: Accessing Access in Perl
by Corion (Patriarch) on Sep 23, 2010 at 13:51 UTC
[reply]
      Do you have a simple example? I'm trying a few approaches and none seem to work (the directory for the mdb specified below may differ from what I originally posted). For example, 
      DBI->connect( "dbi:ODBC:driver=Microsoft Access Driver(*.mdb);dbq=C:/D
+ownload/tmp/examples.MDB",'','') or die "Cannot open the file";

      [download]
      I get the error : DBI connect('driver=Microsoft Access Driver(*.mdb);dbq=C:/Download/tmp/examples.MDB','',...) failed: MicrosoftODBC Driver Manager Data source name no t found and no default driver specified (SQL-IM002) at testScript.pl line 7 Cannot open the file at testScript.pl line 7.
[reply]
[d/l]

        I use the following:

        $dsn ||= "dbi:ODBC:driver=Microsoft Access Driver (*.mdb);dbq=$dbfile"
+;

        [download]

        Note that I have different whitespace. I assume that whitespace is significant.

        $dbfile is a filename with backslashes, which should not be significant, but you might want to try both.

[reply]
[d/l]
[select]

        I think you need to put {} around some key values in your connection string - they quote the value. Also you slashes are the wrong way around in the filespec.

[reply]
Re: Accessing Access in Perl
by locked_user sundialsvc4 (Abbot) on Sep 23, 2010 at 14:17 UTC

    Both ODBC and DAO work well for MS-Access.   Be sure that you have an up-to-date version of that software on the computer you are using.

    As far as I am aware, you must have a copy of Access on your computer ... although you might find that you can reach the file using Excel, which would mean that you at-least have JET available.

    If you are using only the JET database engine portion, consider alternatives.   There are equally capable SQL implementations out there for desktops, both from MS and otherwise.   As far as I can see, Jet, good as it is|was, is today a more-or-less moribund technology.   I would make it my business to transition away from it.

      Thanks for the response. I need to be able to put this application on a computer that may or may not have Access, Excel, etc.., installed. Is there a way I can achieve this without having this as a requirement?
[reply]

        Try OpenOffice. Check the FAQ for Access.

        Not a Perl answer and still requires software installation but at least it's Open.

[reply]

Back to Seekers of Perl Wisdom