Before beginning migration procedure ask yourself: it is really necessary to migrate data to MySQL? Because every migration process requires a lot of work to complete it and check results. MySQL offers several benefits towards Microsoft Access: open source, high performance, cross platforming, management of large databases, multiple users access, strong security system and others. If someone is constructing a complex multi-user database system, it might be reasonable to migrate data from Access to MySQL. However, if the original MS Access database is used by single user and the data file is not huge, there is not much benefit of switching to MySQL.
In general, database migration from MS Access to MySQL is consists of following steps:
– export table and constraint definitions from the source MS Access database
– convert them into MySQL format
– load the resulting statements to MySQL server
– export MS Access data into intermediate storage
– convert data into MySQL format
– load it into the target database
– export MS Access queries into plain text
– convert SQL-statements into MySQL format
– load it into the target 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. In the appeared dialog box “Export Object Type” select “ODBC Database” in “Save As” box. Enter a file name or use the suggested one and click OK. Then “Select Data Source” dialog appears with the list of available data sources for ODBC drivers installed on the system. Go to “File Data Source” or “Machine Data Source” tab and double-click the “Connector/ODBC” data source. After these several steps Microsoft Access data will be exported to MySQL. Note that this method does not convert queries and certain constraints. Is this solution enough for your migration project? If MS Access database is not huge and does not have many queries the answer is “yes”. Otherwise, the conversion will require a lot of manual post-processing after automated part of the project is complete. The human factor may cause errors or incomplete results.
Another approach to database conversion from Microsoft Access to MySQL server is to use third-party commercial applications that can automate the entire process. One of most effective solutions having the best price/quality 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 very easy to use wizard-style 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 converts all indexes and relationships between tables, handles Unicode properly and converts MS Access queries into MySQL views.
If there is no direct connection to the destination MySQL server, Access-to-MySQL can export MS Access database into MySQL script file containing SQL statements to create tables and to insert the data. The program can also merge Microsoft Access 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 the product please visit http://www.convert-in.com/acc2sql.htm
