Migrating a database can be an extremely time consuming procedure, specifically for gigantic corporate-scale data warehouses. There is also a risk of data loss or data being corrupted due to human error if handled manually. Automation can solve some of these problems, however, the entire process cannot be automated. It is difficult to automate migration of MS Access forms or things related to Visual Basic Code. Fortunately, though, the rest of database objects can be effortlessly transported into MySQL using tailored tools.
Many various ways for automating database migration from MS Access to MySQL include free of charge scripts to turn-key commercial tools. Guidelines have been added below to help users reading this article choose the perfect companion for this process:
- The converter you select must be able to support all latest updated versions of MySQL running either on Linux/Unix platforms or Windows.
- The tool you select needs to be compatible with the most grossing MySQL forks for example MariaDb or Percona.
- The tool may have the ability to transport all basic database objects: table definitions, indexes, data, relationships.
- It must be able to sync pre-existing MySQL data with new added MS Access data that is being converted.
- The tool needs to be able to turn the MS Access database into MySQL script file for situations where direct connection is not possible with the MySQL server.
- It must have the feature to execute command line arguments. This is necessary for scripting and scheduling the database migration.
Luckily, we have found the perfect tool for you. Developed by a software vendor present since the last 20 years and an expert at database migration: the Intelligent Converters company has developed a tool to migrate data to MySQL from MS Access with ease. It can meet all requirements and in addition is able to migrate results of SELECT-queries. This beneficial addition can assist in completing the jobs mentioned below.
- In extracting and renaming particular columns, like you can select and rename the column before handling it from for example:
SELECT f1 as document_id, f2 as document_data FROM docs;
2. You can bypass the rows containing NULLS, to do this use:
SELECT* FROM people WHERE name is not NULL;
- You can filter rows before converting them:
SELECT * FROM orders WHERE OrderDate>= #2012-01-01# AND OrderDate< #2013-01-01#
Usually, converts have a very old difficult to read user interface. However, MS Access to MySQL converter has an excellent UI for simple and easy use. To avoid inconvenience, data is conserved into a profile so it can be reused for the next time.
For trial basis, you can test the converter for free. It will let you however only convert 50 records per table and will not let you convert foreign keys and views. The settings can be tailored to fit the users needs.