Microsoft Access is a widespread database management system designed to store structured data within the native file system. MS Access has an easy interface and easy-to-use software system development tools that make it a decent option to create a database for a little cluster of users.
Sadly, the system is bound to some limitations like reducing the scalability 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 the risk of data loss or corruption owing to the human 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
- In the top-level menu ‘File’ select item ‘Export’
- In the dialog ‘Export Object Type’ choose ‘ODBC Database’
As soon as the steps above are completed, MS Access data is imported 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 converter works with all versions of MS Access and MySQL including MariaDB and Percona forks as well as SAAS variations. 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. All indexes and relationships between tables are converted.Access to MySQL converter supports Unicode and migrates 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.
