Documentation Guide - omiltoro/CDM-clinic-app GitHub Wiki

Step 1 – Document Access Sources Documenting an existing Access database is a very easy thing to do. Within Access 2007 itself, you can use the Database Documenter feature that interrogates your Access database and creates a nicely structured report detailing all your database objects and associated metadata, any queries, forms, or reports you’ve created, and then exports the set of documentation out to whatever format you choose (e.g. text file, MS Word RFT file).

Step 2 – Design MySQL Targets Once the Access source metadata has been obtained and digested, the next step is to design the MySQL target database. This basically involves translating the source objects and their properties (such as column datatypes) to MySQL complements. As one can imagine, this step can be extremely time consuming and error-prone if attempted by hand as some Access databases may have many objects that must be converted.

Step 3 – Run Migration to MySQL

Once the source Access metadata has been understood and the MySQL target database designed, the next step is to run the actual data migration process. The extract, transform, and load (ETL) stage can be quite elaborate depending on what one wants to accomplish, and in addition to the MySQL Migration Toolkit, there are many heavy-duty third party ETL tools on the market that offer extreme flexibility in just how to move, aggregate, map, and transform data from Access to MySQL databases. Further, Open Source ETL tools such as Pentaho and Talend have free versions of their products that can handle most data movement/transformation use cases. For simple and uncomplicated migrations to MySQL, the export function of Access can be utilized. You can export each table to an ODBC MySQL datasource (that you define ahead of time), and Access will take care of exporting both the table structure and data out to a MySQL database. Note, however, that if many objects are involved, it can be a tedious process to individually export each table out to MySQL. Also the export function does not export indexes defined on the Access table out to MySQL.

The MySQL Migration toolkit can come to the rescue as a more efficient option over the Access export utility as it can migrate many objects at once (including indexes and data), offers a number of options for those needing to customize the migration operation, and the price can’t be beat as the Migration Toolkit is free and published under the open source GPL license.