How to Control Results of MS Access to MySQL Migration

First, it is necessary to understand which objects must be validated in the destination database after migration is complete:

• Table definitions
• Data
• Indexes
• Relationships between tables (foreign keys in MySQL)
• Queries (views in MySQL)

Table definitions

In MS Access all tables are listed in 'Tables' node of the treeview in the left pane. Right click on the table which definition you need to explore and select 'Design View' menu item. Then you will see the window containing definitions of all table columns. MySQL allows viewing table definition as follows:

• In mysql console client run SQL statement DESC table_name
• In phpMyAdmin highlight the table in the left pane and go to 'Structure' tab

In order to check that table definitions are converted properly, you have to compare each column definition in MS Access and MySQL tables. Type, size and default value of each MS Access column must be converters into the appropriate equivalent in MySQL according to this table:

Data

Accuracy of data conversion can be validated by visual comparison of certain fragment from MS Access and MySQL tables. MS Access allows browsing a table by double-click on its name in the left pane. In MySQL you can do the same as follows:

• In mysql console client run SQL statement SELECT * FROM table_name LIMIT start_record, number_of_records
• In phpMyAdmin highlight the table in the left pane and go to 'Browse' tab

Also, it is necessary to verify that all records have been converted by comparing count of rows for each table in source and destination databases. Microsoft Access indicates number of records at the bottom of the table browsing window. MySQL allows to get count of rows in a table in the following way:

• In MySQL console client run SQL statement SELECT COUNT(*) FROM table_name;
• In phpMyAdmin highlight the table in the left pane, go to 'Browse' tab and number of records will be displayed in the status line Showing rows ... (number_of_records total ...) at the top of the window

Indexes

MS Access allows to browse indexes of the table in 'Design View' (see 'Table Definitions' sections above for details about this option). Click 'Indexes' button in MS Access toolbar and indexes will be displayed in new window with all attributes.

In MySQL you can list indexes as follows:

• In mysql console client run SQL statement SHOW INDEXES FROM table_name;
• In phpMyAdmin highlight the table in the left pane, go to 'Structure' tab and all indexes will be listed right after table structure

Relationships between tables (foreign keys in MySQL)

In Microsoft Access all relationships between tables can be viewed as diagrams. Highlight a table in the left pane, go to 'Design' menu and click 'Relationship Report' button in the toolbar. Double click on any relationship indicator (line between two tables) to view its properties.

MySQL allows to view information about foreign keys as follows:

• In MySQL console client run SQL statement SHOW CREATE TABLE `table name`
• In phpMyAdmin highlight the table in the left pane, go to 'Structure' tab and click 'Relations view' link below the table definition

Queries (views in MySQL)

The first step is to explore MS Access queries in the form of SQL SELECT-statements. You can approach it by the following steps:

• Open the query in design view using the 'Design View' button or right-click on the query and select 'Design View' menu item
• Select 'View' from the menu and select 'SQL View' option

MySQL can extract information about views using this query:

SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';

Unfortunately, there is no other way to check that queries were converted properly besides manual comparing SELECT-statements of each MS Access query and corresponding MySQL view. The task requires deep knowledges in both Microsoft Access and MySQL dialects of SQL.