I'm currently taking a Database class as part of my requirements for my M.S. in Computer Science. Several of our assignments are based on a database provided to us as a Microsoft Access Database. While I have a Windows 7 Virtual Machine, and could install Office in it, I prefer to use free software whenever possible, so I looked for a way to use this database with free software.

Fortunately, the database is in the earlier .mdb format, and not the newer .accdb format. I first found a glimmer of hope in an article by Niall Donegan describing the use of the MDB Tools package.

While the steps posted by Niall worked, and worked well, there are a couple of quirks in MDB Tools that took some working around. Additionally, the steps are kind of repetitive. So I decided to write a small wrapper script for mdb-tools to export the data as a MySQL script. The script takes one argument (the name of the mdb file you're working with) and outputs the SQL script on standard output. So, for example, you might use it as: mdb2mysql students.mdb|mysql students. Here's the script (I call it mdb2mysql) itself:

#!/bin/bash
 
if [ $# -lt 1 ] ; then
        echo "Usage: $0 [mdbfile]" > /dev/stderr
        exit 1
fi
 
MDB="$1"
 
# Extract the schema/DDL
mdb-schema $MDB mysql | sed 's/DROP TABLE/DROP TABLE IF EXISTS/'
 
# Extract table data
mdb-tables -1 $MDB | while read TABLE ; do
        mdb-export -I $MDB $TABLE | sed 's/$/;/'
done

Hopefully this helps others who just need to extract their data from an Access Database. It should be noted that this only gets the schema and data, and does not include foreign keys, views, etc.