Before planning database migration, the person responsible for the procedure should carefully analyze all cons and pros of shifting from MS Access to MySQL or one of its forks. This is step is really important because every database migration requires a lot of work to handle it, validate results and update the application layer. MySQL, MariaDB or Percona offers several benefits towards Microsoft Access: high performance, cross platforming, multiple users access, strong security system and others. If someone is constructing a complex multi-user database system or planning to transfer the database infrastructure into cloud, it might be reasonable to migrate data from Access to MySQL. However, if MS Access database is used by single user and the data file is not huge, there is not much benefit of switching to MySQL. 

Migration of databases from Microsoft Access to MySQL, MariaDB or Percona is consists of the following steps: 

• export MS Access table and constraint definitions, convert them into MySQL format and load to the target MySQL server
• export MS Access data into intermediate storage, make necessary conversions and load it into the target database
• export Microsoft Access queries into the appropriate SELECT-statements, convert them into destination format and import into MySQL database

Some of these steps can be automated. For example, Microsoft Access can export the data to a variety of formats including ODBC. Using this feature, it is possible to export MS Access table to MySQL via Connector/ODBC driver provided by MySQL. Click on table to export and then select “Export” item of the “File” menu. Then the dialog box “Export Object Type” must appear, select “ODBC Database” option in that window. After several steps Microsoft Access data will be exported to MySQL. This method does not convert queries and certain constraints. Is this solution enough for your migration project? If the database is not huge and does not have many queries the answer is “yes”. Otherwise, it may require a lot of manual post-processing after automated part of the project is complete. 

Another approach to database conversion from Microsoft Access to MySQL server is to use dedicated software that can automate the entire process. One of most effective solutions is Access-to-MySQL migration tool produced by Intelligent Converters. Since 2001 the company offers wide range of tools for data conversion, migration and synchronization between all popular databases like MySQL, SQL Server, Oracle, MS Access, PostgreSQL, FoxPro, SQLite and IBM DB2. 

Being easy to use tool Access to MySQL converter  provides enough capabilities to migrate very large and complex MS Access databases with average performance of 2 thousand records per second on a modern platform. The results of migration are quite accurate because the program handles all Microsoft Access types and attributes in intelligent manner doing all necessary data transformation. Also, it migrates indexes and relationships between tables, handles Unicode and translates MS Access queries into MySQL views. Access-to-MySQL converter works with both on premises and cloud variations of MySQL and also support its popular forks such as MariaDB and Percona. 

If there is no direct connection to the destination MySQL server, Access-to-MySQL can export MS Access database into MySQL script. The program can also merge data into an existing MySQL database and synchronize the target database with MS Access data. It supports command line in order to automate, schedule and script the migration process. 

To learn more about Access to MySQL converter please visit the official homepage of Intelligent Converters.