Howto dbi:odbc connect linux->ms access

hexfusion
hexfusion used Ask the Experts™
on
I am trying to setup an odbc connection from a box running centos 5.4 to a windows 2003 server.  I am having a hard time completely understanding how this takes place.

Currently I have current versions of  DBI and DBD:ODBC as well as unixODBC installed.  My thoughts were that ODBC on the win box would allow connections from linux but I can't seem to get this to work.  Any help would be grateful.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Can you show us your code and the errors/warnings that you're receiving?

Author

Commented:
I want to verify this is even possible it seems I would need to use DBD::Proxy to pull this off.  I honestly don't have any code I am using at the moment I just want to get a solid possible yes/no and example.
Since I don't know anything about your network setup, I can't say if you need to use DBD::Proxy.

If your database on the Windows server is setup correctly to allow remote connections, then DBD::ODBC will handle things.  Your starting point should be to create a short script that use DBI and DBD::ODBC and does nothing more than establishes the DB connection.  Once you have that working, then you can move on to the queries.
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

If you need example, then start with the ones that are in the documentation for the modules.

DBI:
http://search.cpan.org/~timb/DBI-1.609/DBI.pm

DBD::ODBC
http://search.cpan.org/~mjevans/DBD-ODBC-1.23/ODBC.pm
unixODBC is a driver manager.  You need the actual ODBC driver for the particular database that you want to work with installed along with it for it to be of much good.  To connect to Microsoft access, you'll need an Access/Jet ODBC driver installed.  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.  Because it's a file-based driver (http://msdn.microsoft.com/en-us/library/ms715395(VS.85).aspx) and because the Jet database format is proprietary, Microsoft Access ODBC drivers for unix are few and far between.  EasySoft sells one (http://www.easysoft.com/products/data_access/odbc-access-driver/index.html?location=Easysoft%20Data%20Access%20body).  I'm not aware of any free or open source ones but I haven't checked in a few years.

Without a unix-based jet odbc driver, you're going to need to use some sort of bridge or proxy.  Something  that sits on the windows box listening for requests from your unix box that knows how to deal with the access database file format--  DBI::Proxy or an ODBC-bridge product (one for which you've got a unix ODBC driver installed).  SQL server express works well as a bridge-- add your access database as a linked server under SQL server express and then use FreeTDS and DBD-Sybase to talk to the SQL server.
So a short yes/no... Yes. It's possible to just use DBD::ODBC, but to do it you'll need to install/purchase the actual ODBC driver (easysoft) and your unix box will  need to have access to the actual database file (typically a samba mount).

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial