Migrating a Microsoft Access database to MySQL

http://25yearsofprogramming.com/blog/2010/20100718.htm

25 Years of Programming
An open source source for C, C++, OWL, BASIC, MDB, XLS, DOT, and more…

Home Projects Sitemap Search Blog Forum+Chat About Us Privacy Terms of Use Feedback FAQ Images Services Payments Humor Music

Translate
Select Language
Gadgets powered by Google

BigBook of S.AP Upgrades Get The Big Book of SAP Upgrades. Dozens of Real-World Tips For Free! Legacy Modernization Rehost/replatform Natural programs and data – lower TCO up to 80% VSAM-SQL Server Migration Migrate VSAM To SQL Srvr With Ease NeoData Can Help. Rapid ROI.

PanayaInc.com/Big_Book_of_Upgrad

www.clerity.com www.alchemysolutions.com

Migrating a database from Microsoft Access toMySQL
There are basically four ways to migrate a database from Access to MySQL: 1. Use a MySQL (or other) utility program to pull the structure and data out of the .mdb file. 2. Use Access to push the structure and data into a MySQL database via ODBC. 3. Create the new empty MySQL database and its table structures manually with hand-coding; export the data from Access to a text file; import from thetext file to MySQL. 4. Use a commercial converter program.

1) Pull the structure and data out of an .mdb file
Windows
I found the now-deprecated MySQL Migration Toolkit to be very helpful for migrating about 15 databases from Access to MySQL. The version I had was for Windows. After I changed a couple of global settings in Access and a couple of specific settings in the .mdb file to bemigrated, the Migration Wizard read from the .mdb file, created corresponding table definitions using MySQL data types equivalent to the Access ones, and transferred all the table data. Although it was nearly 100% unsuccessful at migrating Access Queries to MySQL Views (I did that by manually copying the SQL code out of Access and hand-editing it), the Migration Toolkit’s ability to migrate the tablessaved time and was a lot better than ho help at all. Unfortunately, the Migration Toolkit has been discontinued with no replacement available yet in MySQL Workbench, so right now « no help at all » is what’s available, but that could change at any time. If you are still using an old version of MySQL and its GUI Tools package that has the Migration Toolkit, I’d suggest using it to do your migrationsbefore upgrading to Workbench.

Linux
The MDB Tools package for Linux (one Ubuntu version) has programs to extract table structures and data directly from an Access .mdb file. I tested the programs but haven’t used them for a migration.
mdb-export mdb-schema mdb-sql mdb-tables mdb-ver Export data in an MDB database table to CSV format. Generate table creation SQL code. Output formats: access,sybase, oracle, postgres, mysql. Interactive SQL interface to MDB Tools. Get listing of tables in an MDB database. Return the format of a given MDB database.

The mdb-schema program has an output option called « mysql » that isn’t mentioned in the

1 sur 7

14/09/10 12:17

Migrating a Microsoft Access database to MySQL

http://25yearsofprogramming.com/blog/2010/20100718.htmdocumentation. That’s the one to use. The command line version of MDB Tools worked for me without errors; I also have an Ubuntu Gnome GUI version that only crashes.

2) Use Access to push the structure and data into a MySQL database
Access-related documentation and help refer often to how an ODBC connection allows you to « access a data source » and query it. It took me a while to realize that it allowsboth read and write access to the data source, which means it gives you the ability to push the data into a second database. The advantages of this method are that you don’t have to manually create the table definition with SQL (as with method 3, below), and you don’t have to transfer the data manually. The drawbacks are: 1) It’s blastedly complicated for the relatively small amount of automation…