Migrate from Access 2010 / VBA

[Michael Torrie]

On 11/29/2012 09:05 AM, Joel Goldstick wrote:
> This looks promising:
> http://www.codediesel.com/data/migrating-access-mdb-to-mysql/

Unfortunately I have not found mdb tools to be sufficient.  You can use
them to convert the schema to sql, and to reveal any mdb password (great
for looking at the data structures of compiled apps), but it can't
handle all the data types properly.  To get data out of an mdb file, I
wrote a simple python program that used pyodbc to get the data.  pyodbc
implements a standard python db api interface.  I opened the access
database file with:


MDB = 'C:/Path/to/frs_or_mdb_file'
DRV = '{Microsoft Access Driver (*.mdb)}'
PWD = 'ifneeded'

conn = pyodbc.connect('DRIVER=%s;DBQ=%s;UID=admin;PWD=%s' % (DRV,MDB,PWD))

curs = conn.cursor()

Then you can run queries with standard python db abi calls in standard
SQL syntax, and it's pretty easy to pull out the data and insert it into
a MySQL or PostgreSQL database.

This is for python on Windows of course, and has to have Access
installed, or at least the access engine.