Access, ODBC, php

Is Access accesible, after all?

How to place a php/PEAR frontend sitting in a Debian box onto a MS Access db on a remote Windows box?
The internet is full of "solutions", that in the end point you to drop Access and use a real DBMS.
Well, as many out there I'm aware that there are some differences between Access and a pile of dung: Access stinks more.
Anyway, there are some cases where the damned .mdb thing cannot be scraped away.

The problem

Say we have a proprietary application using an Access database. We need to interface to that stuff within a LAMP-ish framework, that is, we also have a php-based application that uses its own database (I'm using Firebird in these days), but needs to get some data from the .mdb crapbase.
To make life easier (hee hee hee) the php app obviously sits on a decent *NIX (say, Debian) box, whereas the Access stuff is of course on a Windows box.

A solution

There are several possible solutions out there:

dumping the mdb once and for all and migrating onto a decent DBMS
not feasible, since we cannot do without Access (there is a proprietary application that we need to use)
using an ODBC-ODBC bridge
neat solution, but I only found two pieces of software, and they aren't free neither unexpensive (add pointers to the two bridge software)
access Access file (no pun in it...) directly from *NIX
it seems the only drawback is the instability of working on .mdb files on a shared drive
Here's the recipe to make a .mdb file on a remote (i.e. sitting in the same LAN) windows box available on an *NIX host, via UNIXodbc.

 

Windows-side

On the windows box, place the .mdb file on a shared folder. That's all. Even if this procedure exposes you to windows at the minimum possible level, remember to wash your hand when you are finished muddling with windows stuff.

*NIX-side

On the Debian box, make sure you've installed mount.cifs (is inside smbfs), mdbtools and UNIXodbc. The former will allow yout to mount the windows shared folder, the second will supply the libmdbodbc.so driver (plus a lot of tiny useful pieces of software to deal with Access stuff), the latter will give full ODBC functionality to your *NIX box.
First, you need to mount (man mount.cifs) in a more or less permanent way the windows shared folder on the Debian box.
Then, (I usually do that as root) add an ODBC Access driver: add this to /etc/odbcinst.ini:


 
  1. [Access]
  2. Description = MS Access ODBC Driver
  3. Driver = /usr/lib/libmdbodbc.so
  4. Setup = /usr/lib/libmdbodbc.so

Last, define an ODBC DSN for a that .mdb file (I define DSNs at the user level): add this to userland ~~/.odbc.ini

 
  1. [Access_DB]
  2. Description = Access
  3. Driver = Access
  4. Database = /mnt/data/File.mdb
 

 

Test: try using isql <table> and doing a SELECT...

TODO: try using ODBC DSN within a php script