Although the conversion process 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. To setup a complex or multi-user database system, migration of data from Access to MySQL is therefore highly recommended. However, 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 systems through which any of the above steps can be automated. For example, Microsoft Access can be used to export MS Access data to MySQL database via Connector/ODBC driver developed by MySQL vendor.
To export MS Access Data:
* Click on the table to be exported and select “Export” item from “File” menu
* In the dialog box “Export Object Type” select “ODBC Database”
* After a few more steps Microsoft Access data will be exported to MySQL
This method does not convert queries and certain constraints, 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 Access to MySQL converter tool is user-friendly, and 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 as well as the relationships between tables properly, while migrating all MS Access queries to 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.