Link to home
Start Free TrialLog in
Avatar of jdhoover123
jdhoover123

asked on

Accessing MS Access MDB using Perl DBD::ODBC on Linux with unixODBC

I have a System DSN called "testodbc" on a Windows 2003 server connected to an Access 2003 mdb file called testodbc.mdb. It has one table called "testtable" and one field called "testfield" of type "text".

I would like to perform INSERTS and SELECTS (at a minimum) using the Perl DBI (specifically DBD::ODBC). When I tried "perl -MCPAN -e shell" and "install DBD::ODBC" I got the following:

------
The DBD::ODBC module needs to link with an ODBC 'Driver Manager'.
(The Driver Manager, in turn, needs one or more database specific ODBC
drivers. The DBD::ODBC module does _not_ include any ODBC drivers!)

You need to indicate where your ODBC Driver Manager is installed.
You can do this ether by setting the ODBCHOME environment variable
or by running 'perl Makefile.PL -o odbcdir'.

If you do not have an ODBC Driver Manager you should try to get hold of
the unixODBC packages for your system or build it from source (see
http://www.unixodbc.org).
------

I installed unixODBC using yum, "yum install unixODBC" and had no problems. Most of the instructions (such as the three EasySoft primers linked off of the http://www.unixodbc.org) seem to focus on you already having DBD::ODBC installed, and also seem to focus more on setting up a DSN on the Linux server, and accessing it. I already have the DSN, on the Windows server, so I just need to access it from my Perl scripts on a Linux server.

So, how do we:

1. Get DBD::ODBC installed using the unixODBC Driver Manager
2. Ensure that we have a driver in unixODBC for Microsoft Access to access my testodbc System DSN
3. Come up with the connect script in Perl (like the following, but would port 1433 be right here)
            $dbh = DBI->connect("dbi:ODBC:dbname=testodbc@64.198.xxx.xxx:1433","","");
4. Assign a username and password to the ODBC connection under Windows, so I can put that into my connect string above and keep others from accessing it easily.
5. Once I'm that far, it should be as easy as with any other database using the Perl DBI, unless you can think of any other issues?

ASKER CERTIFIED SOLUTION
Avatar of clockwatcher
clockwatcher

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jdhoover123
jdhoover123

ASKER

I figured that was the case - I was just hoping there was something easy out there that would keep me from having to purchase the bridge. I think we'll actually just work to rewrite the Access database in postgresql, and then use Access where the business needs it for ease of access to the data for an end user.
Getting it set up with SQL server express is fairly easy.   You just add a linked server to your access database:

  http://msdn.microsoft.com/en-us/library/ms188279.aspx

  http://msdn.microsoft.com/en-us/library/ms190479.aspx

Once you've got the SQL server talking to your access database, getting access to the SQL server from unix using FreeTDS is easy and from there DBD-Sybase gives you the perl side of it.

  http://www.linuxjournal.com/article/5732

Replacing the access database with postgresql is a much much better option, if it's really an option.  But if it's not, the above will work, is relatively easy to set up, and free (other than the hour or two that it takes to get everything set up).