In past Microsoft Access was quite popular DBMS for small and medium size databases. It has been designed for easy management of structured data stored in the local filesystem. MS Access has intuitive interface and provides easy-to-use tools for database administration and development purposes. Unfortunately, Microsoft Access also has some limitations that prevent from scaling and deploying the system. Most important of them are 2GB max file size, 255 max concurrent users and others.
Due to these limitations of MS Access many companies and organizations consider migration to advanced database management systems like MySQL. However, database migration requires a lot of resources to complete it properly and to validate results when doing manually.
In general, the process of MS Access to MySQL migration includes the following basic steps:
- generate MySQL CREATE-statements for all definitionsof source tables and constraints and load it to the target MySQL server
- export MS Access data into CSV files as temporary storage, transform each file according to MySQL format (if it is necessary) and load it into the target database
- extract Microsoft Access queries as SQL SELECT-statements, translate according to MySQL syntax and load into the destination database
- convert MS Access forms and VBA code into PHP scripts and HTML pages
Most of these steps can be automated and it can help to reduce risk of data loss or corruption due to human factor. For example, Microsoft Access allows exporting the data into multiple formats including generic ODBC source. Using this feature each MS Access table can be exportedinto MySQL format as follows:
- install MySQL Connector/ODBC driver
- open the database in MS Access and click on table to export
- select ‘Export’ item if the ‘File’ menu
- in the dialog ‘Export Object Type’ select ‘ODBC Database’
After competing these steps, Microsoft Access data appearsin MySQL database. Note, thisapproach does not convert relationships between tables and queries.
Large and complex databases require more comprehensive option to automate migration from MS Access to MySQL such as using dedicated software. One of such software tools is Access to MySQL converter developed by Intelligent Converters. Key features of the product:
- all versions of MS Access and MySQLare supported including forks such as MariaDB and Percona
- table definitions, data, indexes, relationships between tables and queries are converted
- high performance of the conversion – about 2000 records per second on a modern platform
- it allows to script, automate and schedule the conversion process using command line version
- full support for Unicode
Although Access to MySQL converter is easy to use yetit provides enough capabilities to handle large and complicated databases. The program processes all data, attributes and constraints in the most intelligent way doing all necessary transformations.
To learn more about the product, please visit its official page https://www.convert-in.com/access -to-mysql.htm
