MySQL becomes extremely popular as free cross-platform database management system. Many companies consider migration from their legacy or proprietor DBMS to MySQL. One of the possible data sources is Microsoft Access that is a reasonable choice for small or medium size desktop based databases but may be not enough for such demands of growing organization as parallel multi-user access, working over Internet, tight integration with website.
Before migration even starts it is important to understand whether benefits of moving from MS Access to MySQL overcome required efforts and complexity of the process. There are a wide range of MySQL advantages as compared to Microsoft Access: scalability, support across multiple platforms, higher level of security and the performance as well. If the database complexity and size grow over time, it is reasonable to migrate from MS Access to MySQL. On the other hand, when database has just a few users and it size is less than 2GB migration to MySQL does not make sense.
The most common approach to migration from Microsoft Access to MySQL is known as extract-transform-load (ETL) method and it includes these steps:
- The definitions of MS Access tables, indexes and constraints are exported as data definition language (DDL) statements; converted to MySQL format, following which it can be imported into MySQL database.
- The MS Access data is extracted into an intermediate storage such as comma separate values (CSV), converted according to MySQL format and then loaded to the target database.
- MS Access queries are exported into SQL statements, which are translated into MySQL dialect and then imported into the destination database.
The most straightforward approach to database migration is based on Microsoft Access feature of export into any available ODBC formats. Once MySQL ODBC driver is installed, you can export the tables to MySQL right from MS Access interface.
For this purpose,select File à Export menu item, in ‘Export Object Type’ dialog box select the option ‘ODBC Database’. After entering a few more settings the data will be exported to MySQL. However, this approach does not cover queries, indexes and constraints, so it required some post-processing efforts that takes extra time before the migration is completed.
Another approach to MS Access to MySQL database migration is to use dedicated software that can completely automate the overall process. One of such products is Access-to-MySQL developed by Intelligent Converters, software company specializing on databases migration and synchronization between PostgreSQL,Oracle, SQL Server, MySQL, MS Access, FoxPro, SQLite and IBM DB2.
It is easy-to-use tool with wizard style interface that does direct reading and writing databases without any 3rd party components to guarantee high performance. The program converts tables, data, indexes, relationships between tables and queries accurately with respect to all differences between MS Access and MySQL.
When there is no direct connection to the MySQL server, Access-to-MySQL can export database into a local SQL script file that can be uploaded to the target server and then manually imported into the database. The tool allows to merge or synchronize by primary key MS Access data with an existing MySQL database. Command line version of the converter allows to script and schedule the database migration.
Find more information about MS Access to MySQL converter on the official site: https://www.convert-in.com/access-to-mysql.htm
