Database migration from MS Access to MySQL

Although Microsoft Access is user friendly easy-to-use database management application, it hardly can operate large and complicated date warehouses. So, many organizations split their databases in front-end and back-end parts, keep MS Access as a front-end and migrate data and logic to another DBMS providing native multi-user environment with more stability and application performance.

MySQL is often considered as back-end solution due to such benefits as high performance, multi-user access, cross platforming, strong security system and low total cost of ownership (TCO).

The following method known as extract-transform-load (ETL) approach can be used to migrate data from MS Access to MySQL:

• MS Access table, index and constraint definitions are exported as CREATE-statements and converted into MySQL format, then loaded to the target database
• The source data is exported into intermediate storage, converted according to MySQL format and loaded it into the destination database
• Microsoft Access queries are exported into CREATE-statements, those statements are converted to comply with SQL dialect of MySQL and then imported into the destination database

Some steps of the ETL method specified above can be automated. For example, MS Access can export the data into MySQL database through Connector/ODBC driver provided by MySQL vendor. To implement it follow these steps:

• Highlight the table for export and select “File” > “Export” menu item
• In the appeared dialog box with title “Export Object Type” select “ODBC Database”
• After a few well explained wizard-style steps MS Access data will be exported into MySQL

Although this straightforward approach does not convert MS Access queries and some constraints, it is easy to use and sufficient to migrate of small databases. Large and complicated databases require manual post-processing after completion of the automated part.

Fortunately, there is a special software that can completely automate MS Access to MySQL database conversion even for big databases having a lot of queries. One of these tool is MS Access to MySQL offered by the Intelligent Converters, a software company specializing in data migration and synchronization between all the popular database management systems such as MySQL, PostgreSQL, MS Access, SQL Server, Oracle, SQLite, FoxPro and IBM DB2.

MS Access to MySQL converter is an easy-to-use wizard style tool providing enough capabilities and features to migrate all MS Access entries into the destination database with average performance of 10000 records per second on a modern hardware platform. The program supports Unicode, converts all indexes and relationships between tables with related attributes, migrates MS Access queries to MySQL views.

Besides the main features specified above, the migration tool can export MS Access database into a local MySQL script file for those cases when MySQL server does not allow remote connections. The resulting script file can be imported into MySQL database later via the standard tools like mysqldump or phpMyAdmin. MS Access to MySQL converter can also merge data into an existing database, synchronize the destination database with source data. Finally, it supports command line to enable scheduling and scripting the database migration process.