Friday, June 14

Converting database from MS Access to MySQL

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:

  • Extract the definitions of MS Access tables, indexes and constraintsand convert them into MySQL format.Then load converted statements to the MySQL database.
  • Export MS Access data to intermediate storage, make necessary conversions and load it into MySQL database.
  • Export MS Access queries into SQL statements; convert to the MySQL format and then load into the target database.

There are software products that can fully or partially automate the steps specified above. For example, Microsoft Access can be used to export data from MS Access 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 MS 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.

MS Access to MySQL converter is a user-friendly tool that is capable of taking complex and very large MS Access database migrations with a performance of up to 10000 records per second on the modern platform. The tool converts table definitions with all necessary attributes (like default values, null allowance, etc), data, indexes and relationships between tables. Its results are also of high accuracy because the program handles all migration processes in an intelligent manner. MS Access to MySQL converter handles Unicode and migrates all MS Access queries to MySQL views.

Also,MS Access to MySQL converter 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.

Visit the official page of Intelligent Converters to learn more about MS Access to MySQL migration tool.