Migrating an Access Database to MySQL21 Sep 2011 in Linux
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.
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.