Exploring MS Access to MySQL database migration

In past Microsoft Access was quite popular DBMS for small and medium size databases. It has been designed for easy management of structured data stored in the local filesystem. MS Access has intuitive interface and provides easy-to-use tools for database administration and development purposes. Unfortunately, Microsoft Access also has some limitations that prevent from scaling and deploying the system. Most important of them are 2GB max file size, 255 max concurrent users and others.

Due to these limitations of MS Access many companies and organizations consider migration to advanced database management systems like MySQL. However, database migration requires a lot of resources to complete it properly and to validate results when doing manually.

In general, the process of MS Access to MySQL migration includes the following basic steps:

  • generate MySQL CREATE-statements for all definitions of source tables and constraints and load it to the target MySQL server
  • export MS Access data into CSV files as temporary storage, transform each file according to MySQL format (if it is necessary) and load it into the target database
  • extract Microsoft Access queries as SQL SELECT-statements, translate according to MySQL syntax and load into the destination database
  • convert MS Access forms and VBA code into PHP scripts and HTML pages

Most of these steps can be automated and it can help to reduce risk of data loss or corruption due to human factor. For example, Microsoft Access allows exporting the data into multiple formats including generic ODBC source. Using this feature each MS Access table can be exported into MySQL format as follows:

  • install MySQL Connector/ODBC driver
  • open MS Access, load the database in and click on table to export
  • next step is to highlight ‘Export’option of the ‘File’ menu
  • finally, select ‘ODBC Database’ option in the ‘Export Object Type’ window

After competing these steps, Microsoft Access data appears in MySQL database. Note, this approach does not convert relationships between tables and queries.

Large and complex databases require more comprehensive option to automate migration from MS Access to MySQL such as using dedicated software. One of such software tools is MS Access to MySQL converter developed by Intelligent Converters. Key features of the product:

  • all versions of MS Access and MySQL are supported including forks such as MariaDB and Percona
  • table definitions, data, indexes, relationships between tables and queries are converted
  • high performance of the conversion – about 2000 records per second on a modern platform
  • it allows to script, automate and schedule the conversion process using command line version
  • full support for Unicode

Although Access to MySQL converter is easy to use yet it provides enough capabilities to handle large and complicated databases. The program processes all data, attributes and constraints in the most intelligent way doing all necessary transformations.