This whitepaper explores generic challenges, approaches and automation tool for database migration from MS Access to MySQL.
As the complexity of the database grows, major experts recommend migrating from file databases such as MS Access to full-fledged relational DBMSs based on client-server architecture, such as MySQL. On the other hand, Microsoft Access is still suitable as a desktop database engine for small volumes of data and single or few users because the DBMS benefits wouldn’t be at full potential for such cases.
Scalability, open source, ease of use, advanced security, high performance, and cross-platform compatibility are key reasons to choose MySQL over many other database systems available on the modern IR scene. However, the difficulties of the migration procedure are enough to discourage anyone from converting MS Access to MySQL.
Generic challenges involved in the migration:
Convert definitions of MS Access tables, indexes and constraint in to MySQL format handling safe type mapping and correct translation of the related DDL statements. Types mapping is basically trivial for these two DBMSs except the following cases:
- Memo is converted into TEXT or LONGTEXT
- Auto-number is converted into INT AUTO_INCREMENT
- Yes/no is converted into TINYINT(1)
- OLE Object is converted into LONGBLOB
- Hyperlink is converted into any MySQL text data types
Export and convert MS Access data in the compatible form for import into MySQL database. This step requires proper recognition and migration of binary data through its text representation.
Convert MS Access queries into MySQL views via export in form of CREATE statements and further translation according to MySQL syntax.
Due to complicated nature of the challenges specified above, it is reasonable to convert MS Access to MySQL via special automation tools instead of spending lot of time and resources on manual approach.
Those steps can be half-automated using ODBC drivers connecting Microsoft Access with other DBMS. For example, MySQL comes with family of ODBC drivers called Connector/ODBC that provide access to database through the industry standard API. For convenience, simply select "File → Export." Next, from the "Export Object type" dialog box that appears, choose the "ODBC Database" option. After a few more prompts, MS Access data will be exported into the destination MySQL database. This method does not convert queries and constraints. Also, the post-processing step may need some work; therefore, this half-automated approach still can take a long time.
Fully automated approach to convert MS Access to MySQL is based on using dedicated cutting-edge software for overall database migration. One of the software vendors providing such tools is Intelligent Converters company focusing on database migration and synchronization for more than 20 years.
They offer extremely easy to use wizard-style tool yet powerful enough for efficient database migration at the rate of more than 10,000 records per second on an average modern hardware. The program handles bulk migration of large and complicated databases with high accuracy providing safe types mapping and necessary conversion of data to comply with the target MySQL format.
Key benefits of MS Access to MySQL converter by Intelligent Converters:
- All versions of MS Access and MySQL are supported including SaaS platforms (Azure for MySQL, AWS) and forks (MariaDB, Percona)
- All major database entries are migrated (schemas, data, indexes, constraints, relationships between tables, queries)
- Option to export MS Access database into script file that includes statements to create database objects and fill tables with the data (suitable for those cases when MySQL server does not allow direct connection)
- Merging and synchronization with previously migrate data. This option requires identical structures of MS Access and MySQL tables. Also, for synchronizing purpose both source and destination tables must have primary key built on the same columns.
- Convert MS Access queries into MySQL views handling all major differences in SQL syntax between these two DBMSs:
- convert operators of adding strings 'expr1 & expr2' and 'expr1 + expr2' into ‘CONCAT (expr1, expr2)’
- replace all references to aliases with actual expression hidden behind the alias since MySQL does not support this feature
- all date values are converted from MM/DD/YY to YYYY-MM-DD format
- logical constants ‘Yes’ and ‘No’ are replaced by ‘1’ and ‘0’ correspondingly
- many others
- Command line support gives opportunity to script and schedule MS Access to MySQL migration
- All conversion settings such as source and target database names, MySQL connection details, MS Access password and others, are stored into profile to simplify running recurrent migrations.
- Quick Launch. Once the profile is created, the migration can be launched within single click on a profile in Windows Explorer.
- Intuitive interface, comprehensive help system and full install/uninstall support
Find more details about how to convert MS Access to MySQL and the related software products by visiting the official site of Intelligent Converters.
