Moving data from desktop DBMS to cloud warehouses is a long-term modern trend. It is not surprising that many organizations consider migration from MS Access to more powerful distributed database management systems like MySQL and its variations.
There are many free and paid options to convert Access to MySQL. One of the most straightforward methods is based on MS Access itself and MySQL ODBC driver also known as Connector/ODBC. Follow these steps to migrate MS Access to MySQL according to this approach:
Source: stpatricks.ie
- Open the database in MS Access and navigate to “All Access Objects” pane on the left
- Right-click on the table to migrate and select Export > ODBC Database menu
- In the appeared Export dialog box enter the destination MySQL table name
- Then the dialog box “Select Data Source” will appear with list of all available ODBC drivers
- Find Connector/ODBC DSN entry that corresponds to the target MySQL server, enter login and password if it is necessary
- If the export succeeded, a dialog box with a success message will appear.
Although, the approach specified above is a truly working method, it involves too much human interactions. Every database professional understands that data migration is a hard procedure demanding a lot of resources for complex corporate-scale warehouses. It is not affordable to run the task manually or even semi-manually due to high risk of data loss or corruption.
Fortunately, there are automation tools to convert Access to MySQL that can migrate a database smooth and safe through just a few clicks of mouse buttons. Those tools usually process all database objects except forms and Visual Basic code and work with on-premises and cloud MySQL hosted on Unix and Windows platforms.
Source: samelane.com
Database migration solutions to convert Access to MySQL are ranged from scripts for IT professionals to turn-key commercial tools that can be used by everyone. In order to find the right tool, the following features must be verified:
- The migration tool must support all modern versions of MySQL and its popular forks such as MariaDB and Percona
- The converter must handle main database objects such as tables, data, indexes, constraints and queries
- The product should be able not only to create new MySQL database, but also to merge or synchronize existing MySQL database with MS Access data
- The migration tool must support converting Microsoft Access database into MySQL script file for those cases when direct connection to the target server is not available
- And finally, the product must support command line arguments for scripting and scheduling the database migration procedure
One of these tools to convert Access to MySQL has been developed by Intelligent Converters, a software company working in database migration field since 2001, meets all requirements listed above. Moreover, the product can migrate not only table data but also row sets of SELECT-queries. This flexible capability can be used for the following tasks.
Source: computerworld.com
- Migrate particular columns with renaming: SELECT FName as FirstName, LName as LastName FROM Table1
- Exclude from migration rows containing NULL values: SELECT * FROM Table2 WHERE not isnull(Comments)
- Filter rows to migrate by some criteria: SELECT * FROM Table3 WHERE ID > 1000
MS Access to MySQL converter has intuitive user-friendly interface and comprehensive help system that makes it useful for wide range of users, not only for database professionals. The product stores all conversion settings into profile for reusing it in next run. Finally, it implements advanced migration algorithm for MS Access queries that allows to handle more than 90% of all syntax pattens.
This database migration tool to convert Access to MySQL is supplied with trial version that converts only 50 records per table and does not convert foreign keys and views. As every software product provided by Intelligent Converters, MS Access to MySQL converter is supplied with one-year subscription for updates and 24/7 technical support.
