How to Control Results of MS Access to MySQL Migration

[Access to MySQL Converter]  [Types Mapping]  [Converting Queries

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

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 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:

MS Access MySQL
Text VARCHAR(n), where n is size of Text column
Memo TEXT
Byte TINYINT UNSIGNED
Integer SMALLINT
Long INT
Memo TEXT
Single FLOAT
Double DOUBLE
Currency DECIMAL(13,4)
AutoNumber INT AUTO_INCREMENT
Date/Time DATE or TIME or DATETIME depending of column's semantic
Yes/No BIT(1) or BOOL
Ole Object LONGBLOB
Hyperlink VARCHAR(255)
Replication ID (guid) VARCHAR(38)

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:

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:

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:

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:

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:

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.