Nowadays more and more organizations find it difficult to use of Microsoft Access to store their growing data. To avoid limitations of that database management system, they consider migration to more powerful yet affordable DBMS such as MySQL that would enhance business proficiency and reliability. This whitepaper is a step-by-step guideline on migration from MS Access to MySQL. 

Main Reasons for Database Migration

MySQL database management system has numerous advantages towards MS Access:

Deployment: MySQL can actually be integrated with MS Access. There is option to take MySQL as a data storage while MS Access application is used as a front-end for data manipulations. For instance, certain users can cope with the data by applying the standard MySQL client programs, and others may work with GUI applications. Integrating web servers like Apache is best done with MySQL. This is why developers can work with it on any modern script languages like PHP, Perl, Python etc. What’s best? All of MySQL components are provided free of charge, the database can be accessed through web browsers that gives benefits of platform-independency.

Multiple-user access. MS Access provides simultaneous access to the data on file level, and this is limited to local use or across the corporate network. On the other hand, MySQL can handle multiple users access, granting different permissions on the databases.

Security. MS Access files can be said to be sort of insecure, as anyone can access its data. Though there is a password option for the database, it could still be easily broken into by uncertified users. But MySQL server has a stronger security management over its files as it grants permissions and roles for relational DBMS.

Cost. MS Access is a commercial application. MySQL is distributed under open source license, and has free tools that can equally reduce the bills attached to the TCO of administration and development for the database.

Approaches to Migration 

The simplest way to migrate from MS Access to MySQL is known as extract-transform-load (ETL) method. Following this approach, the data is extracted from MS Access database, converted it to MySQL format and then loaded into the destination database. Note that ETL method requires installation of MySQL Connector/ODBC driver. Other options to implement this migration are listed below. 

Use Microsoft Access to Export Data

This scenario is based on exporting MS Access data as a text file. On the next step that data is imported into MySQL database via ‘LOAD DATA’ statement or mysqlimport tool coming as a part of standard MySQL installation. This option does not require special conversion tools, even if there is no MySQL client available, simply create data file and transfer to MySQL machine. The only prerequisite required for this approach is that MySQL tables must be manually created before data is loaded.

Generate MySQL Scripts

The other option for exporting files is to use a script that reads source tables and converts same into MySQL script files. Normally, the scripts should contain SQL options to create tables and for filling, and editing the tables. Here, database admin can import script files to MySQL with the console client or phpMyAdmin.

Commercial Tools

This is obviously the easiest option of database migration from MS Access to MySQL since the dedicated software automates the entire procedure with just a few clicks of mouse buttons. The most important point of this approach is to choose migration tool properly. It must provide basic capabilities to migrate databases in smooth and efficient way: 

• All modern versions of MySQL must be supported including forks and SAAS variations
• It does not require Microsoft Access, ODBC drivers or any other software installations
• Main database objects (table definitions, data, indexes, constraints) must be converted 
• MySQL table types, charsets and other conversion settings must be customizable
• Full support for Unicode
• Command line 

One of migration tools that meets all requirements above is Access to MySQL converter developed by Intelligent Converters. Besides, it comes with 1-year subscription for updates, 24/7 unlimited support and option of customization according to customer requirements.