Although the database migration is a tedious task, the MySQL has considerable benefits over the Microsoft Access, most notably: higher performance; multiple user access; cross platforming; and a better security system. Moving MS Access data and applications to MySQL can help setup a complicated concurrent database management system with highly level of security and performance. On another hand, for a single user system where there is not so large data file involved, the MySQL offers not much difference over the MS Access database.
To migrate data from MS Access to MySQL do the following:
- Export and convert the MS Access table and constraint definitions to MySQL format then load to the target MySQL server;
- Export MS Access data to intermediate storage, make necessary conversions and load it into the target database;
- Export MS Access queries into SQL statements; convert to the MySQL format and then load into the target database.
There are number of solutions to automate the migration. For example, Microsoft Access can be used to export MS Access data to MySQL database via Connector/ODBC driver developed by MySQL vendor.To migrate from Microsoft Access to MySQL using this option take the following steps:
- Highlight the table name and select “File” >”Export” menuitem
- Select “ODBC Database” option in the appeared dialog box
- After specifying a few more settings MS Access data will be migrated to MySQL database
This approach does not handle queries and relationships between tables but is sufficient for the migration of a small-scale database with not too many queries. Otherwise, there will still be need for a manual operation upon the completion of the automated part.
Certain dedicated software can also be used for the MS Access to MySQL database conversion, some of which allows for the automation of the whole process. One of these is the Access-to-MySQL migration tool produced by the Intelligent Converters. The company is dedicated to offering highly effective solutions and up-to-date tools for data migration, conversion and synchronization within all the popular databases including MySQL, MS Access, SQL Server, PostgreSQL, Oracle, SQLite, FoxPro and IBM DB2.
The MS Access to MySQL converter is user-friendlytool that is capable of taking complex and very large MS Access database migrations with a performance of up to 2000 records per second on the modern platform. Its results are also of high accuracy because the program handles all migration processes in an intelligent manner, handling Unicode and converting the indexes.The tool also automates migration of relationships between tables into foreign keys and MS Access queries into MySQL views.
The Access-to-MySQL tool can export MS Access data directly to MySQL script when there is no direct connection to a destination MySQL server. The program is also capable of merging data into an existing MySQL server, synchronizing the target database with MS Access data. Finally, it has a support command line, which enables the automation, scheduling and scripting of MS Access-MySQL database conversion processes.