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

Posted on 2008-11-19
Last Modified: 2013-11-13
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

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 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("","","");
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?

Question by:jdhoover123
    LVL 25

    Accepted Solution

    You'll need an ODBC bridge to access your access database from a unix OS.  Something installed, running and listening for requests on the windows box that knows how to deal with the Access database.  It takes the requests from the unixODBC driver,  connects to the access database and relays back the results.  

    Access (Microsoft Jet database) isn't a client/server type database.  It's simply a file sitting on a file system somewhere.  The Microsoft Access ODBC driver knows the file structure and is capable of taking an ODBC-compliant request and doing whatever it needs to do with the file and returning the results.  It's a file-based driver (  There are no Microsoft Access ODBC drivers written for unix which is why you need the bridge.  The unix box has to connect to something running on the windows box that knows how to work with the Microsoft Access ODBC driver.

    A few companies sell a bridge (EasySoft is one), but the easiest (and least expensive) way to get it to work is to use a SQL Server Express as the bridge.  You set up the access database as a linked database under the SQL Server and connect the unix box to the SQL Server (a few ways to do that but one of the more popular is via FreeTDS and DBD-Sybase).

    Anyway, if you really want to go the ODBC-ODBC route.  You'll need to purchase a bridge product (don't know of any free ones).  Once you've got that installed and running on your windows box.  You'll define a DSN  on your unix box that  connects to the bridge-- what the DSN looks like and how it's defined will depend on the bridge that you've purchased.  The bridge then acts as the client on the windows box connecting to your access database.  It'll use the DSN that you've defined on your windows box to connect to your Access database.  Your unix box and unixODBC don't have any thing (directly) to do with the DSN that you've defined on your windows box.
    LVL 1

    Author Comment

    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.
    LVL 25

    Expert Comment

    Getting it set up with SQL server express is fairly easy.   You just add a linked server to your access database:

    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.

    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).

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
    Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now