Export Microsoft Access tables to MySQL

Question

I have a database that was created in Microsoft Access and I'm trying to replicate it in a MySQL database. Is there some sort of tool that can do it?

Historically, I've exported to CSV and then used a whole lot of replacements to whip it into shape, but I'm looking for something that takes less work.

I'm working on a Windows machine and would prefer free software.

Answer 1

We used to bring 30 remote MS-SQL databases, together with some Access data, into MYSQL so we could do some central reporting and it was an automated process of dumping out CSV files and then imports with some stored procedures to modify the data as requied.

It took some work to get things going, but then the process was a daily, automated run - so don't rule out the csv route. While deciding what to do, we did try some commercial convertor products (dbconvert) - which duplicated things very well but did not give us the flexibility of our scripted work.

If you are just concerned about manual aspects of the conversion, you might want to check out the Open Source shellsql tools that did most of our MS-SQL dumping for us:

http://shellsql.sourceforge.net/

I daresay I could dig out some of the scripts I wrote if you want to investigate further.

Answer 2

I use premiumsoft navicat for mysql admin (including access, excel conversion), which I find very good. There is a lite (free) version but I don't think that the conversion tools are included. For me, as I use mysql a lot, it is well worth the cost.

Answer 3

See http://stackoverflow.com/questions/758669/automatically-generate-sql-from-existing-ms-access-table

Answer 4

If you create a DSN for your target MySQL database, you can then export your Access tables one-by-one to the DSN. Likely the data types won't be exactly what you want, so you'll probably have to do it more than once, but it's one way to get the data into an ODBC database quickly.