Technology

How to Migrate Database from MS Access to My SQL

Written by admin

Microsoft Access is a widespread database management system designed to store structured data within the native file system. MS Access has aneasy interface and easy-to-use software system development tools that make it a decent option to create adatabase for alittle cluster of users.

Sadly, the system is bound to some limitations like reducing thescalability of the database like 2GB file size limit, 255 coinciding users (Microsoft recommends 100), etc. This explains why several businesses and organizations migrate their databases from MS Access to MySQL.

However, data migration procedure needs lots of labor to complete it. Thus, everybody who plan such migration needs to ask himself: it is very necessary to transfer the MS Access database to MySQL server? From one hand, MySQL offers many advantages towards Microsoft Access such as cross platforming, high performance, multiple users access, robust security system and others. From another hand, if MS Access acts as a single-user information storage and also the data volume isn’t large, there’s not a lot of advantage of changing from MS Access database to MySQL.

In general, database migration from MS Access to MySQL is consists of the subsequent steps:

–              Export MS Access table and constraint definitions in kind of DDL SQL statements, convert them into MySQL format and load to the target MySQL server.

–              Export MS Access data into comma separated values files (one file per table), create a necessary conversion of every .csv file and import it into MySQL database.

–              Export MS Access queries into SQL-statements, convert them into MySQL format and load into the target database.

Some of these steps are automated so as to avoid therisk of data loss or corruption owing to thehuman factor or error. For instance, Microsoft Access will export the data to multiple formats together with abstract ODBC source. This feature permits exporting MS Access table to MySQL via the suitable Connector/ODBC driver as follows:

  • Open the database in MS Access and click on the table to export
  • Select ‘Export’ item if the ‘File’ menu
  • In the dialog ‘Export Object Type’ choose ‘ODBC Database’

After these steps, Microsoft Access data will be loaded into MySQL database. One thing to be kept in mind is that that this approach doesn’t convert queries and some constraints.

Another option of database migration from Microsoft Access to MySQL server is to use dedicated software tools. One among these tools is MS Access to MySQL converter made by Intelligent Converters. The program supports all versions of MS Access and MySQL including such variations as MariaDB and Percona. Also, it’s possible to script, modify and schedule the conversion process using command line version of the converter.

Access to MySQL converter combines simple to use interface with decent capabilities to migrate massive and complex MS Access databases (average performance is 2000 records per second on a modern platform). The program handles Microsoft Access types and attributes in an intelligent manner doing all necessary transformations. It handles all indexes and relationships between tables, supports Unicode and converts MS Access queries into MySQL views.

Access to MySQL converter comes with unlimited support and annual subscription for updates. Every client will be receiving new versions of the tool within one year after buying, for free. To find out additional concerning the product, please visit its official page http://www.convert-in.com/access-to-mysql.htm

About the author

admin