[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2363
  • Last Modified:

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?

0
jdhoover123
Asked:
jdhoover123
  • 2
1 Solution
 
clockwatcherCommented:
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 (http://msdn.microsoft.com/en-us/library/ms715395(VS.85).aspx).  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.
0
 
jdhoover123Author Commented:
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.
0
 
clockwatcherCommented:
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).
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now