Database migration from MS Access to MySQL

Microsoft Access is a popular database management system designed to store structured data in the local file system. MS Access has user-friendly interface and easy-to-use software development tools that makes it a good choice to create database for small group of users. Unfortunately, the system has certain limitations reducing scalability of the database such as 2GB file size limit, 255 concurrent users (Microsoft recommends 100), etc. This explains why many businesses and organizations migrate their databases from MS Access to MySQL. However, data migration procedure requires a lot of work to complete it and check results. Thus, everyone who plan such migration should ask himself: it is really necessary to transfer the database to MySQL server? From one hand, MySQL offers several benefits towards Microsoft Access such cross platforming, high performance, multiple users access, strong security system and others. From another hand, if MS Access acts as a single-user data storage and the data volume is not huge, there is not much benefit of converting database to MySQL.

In general, database migration from MS Access to MySQL is consists of the following steps:

  • export Microsoft Access table and constraint definitions in form of DDL SQL statements, convert them into MySQL format and load to the target MySQL server
  • export MS Access data into comma separated values files (one file per table), make necessary conversion of each .csv file and import it into MySQL database
  • export Microsoft Access queries into SQL-statements, convert them into MySQL format and load into the target database

Some of these steps can be automated in order to avoid risk of data loss or corruption due to human factor. For example, Microsoft Access can export the data to multiple formats including abstract ODBC source. This feature allows exporting particular MS Access table to MySQL via the appropriate Connector/ODBC driver as follows:

  • in MS Access highlight table name to export
  • select ‘Export’ item if the ‘File’ menu
  • in the dialog ‘Export Object Type’ select ‘ODBC Database’

After these steps Microsoft Access data will be loaded into MySQL database. Note that the approach does not convert queries and certain constraints.

Another option of migrating Microsoft Access database to MySQL server is to use dedicated software tools. One of these tools is MS Access to MySQL converter produced by Intelligent Converters. The program supports all versions of MS Access and MySQL including such variations as MariaDB and Percona. Also, it is possible to script, automate and schedule the conversion process using command line version of the converter.

Access to MySQL converter combines easy to use interface with sufficient capabilities to migrate large and complex MS Access databases (average performance is 2000 records per second on a modern platform). The program handles Microsoft Access types and attributes in intelligent manner doing all necessary transformations. It handles all indexes and relationships between tables, supports Unicode and converts MS Access queries into MySQL views.

Access to MySQL converter comes with unlimited support and one-year subscription for updates. Every customer will be receiving new versions of the tool within one year after purchasing free. To learn more about the product, please visit its official page.