Connecting to a Microsoft Access database on a Linux machine is one of those things that I'd always been able to work around, but then I had one project when a client needed to be able to upload an Access database to a web site, and then have a job that would pick up all the information from this database an insert it into (in this case) PostgreSQL.

Now strictly speaking it is possible to pull data from an Access database file directly via (for instance) PHP, but I would recommend an intermediary - have the file uploaded, and then pull all the information out and into another database (such as MySQL or PostgreSQL). This way you have much more flexibility - all the benefits of using MySQL or PostgreSQL while still being able to get the data from Access.

So, how to do it? There are some scattered articles on the web, but I thought one more couldn't hurt. This one will be more of a case study than a strict HOW-TO.

I installed the following rpms (I'm on Mandrake 9.1 - which this site is using - using a "minimal install" configuration that doesn't include the X server) using urpmi:

mdb-tools0.5.2-1.mdk
libmdb-tools0.5.2-1.mdk

These are from the MDBTools project, which was designed to allow Unix-based machines to connect to Access. It includes a number of handy command-line utilities to allow for extraction of data from the database. One of these is mdb-sql, a command line sql client for an access database. Having installed the rpms, I simply type mdb-sql and it drops me into a SQL prompt.

If all the planets were aligned, I could have used the mdb-export tool, which dumps all the data from an Access database, but in this case, my database included OLE objects (oy vey!) so this seemed to cause problems for that. One other extremely handy utility that's included with mdb-tools though is mdb-schema. The following command will give you a nice print out of the Access schema which you can use when building the syntax of your queries.

mdb-schema > schema.txt

From there, I then basically set up a bash script employing sed and awk to format data that had been extracted with a mdb-sql command. This then built a sql statement that could be used to populate (in this case) a PostgreSQL database.