How to check MS Access to MySQL migration

After database migration is completed, the person responsible for the process must check that everything has been migrated properly. This whitepaper explains how to validate results of MS Access to MySQL database migration.

First, it is necessary to answer the question: what kind of objects must be validated after database migration is completed? Below are the primary objects to verify:

Schemas DDL (types mapping)
Data
Indexes and constraints
Relationships between tables (foreign keys in MySQL)
Queries (views in MySQL)

Table definitions

Microsoft Access allows to explore all tables in form of tree-view. Highlight the table, right click on its name and select ‘Design View’ menu. This will force opening new window with list all table columns and related properties. In MySQL there are two options to explore table structure:

Run the query DESC `table name` in MySQL console client

Highlight the table in the left pane and go to ‘Structure’ tab in phpMyAdmin

Conversion of table structures can be validated by comparison of column definitions in MS Access and MySQL tables. Size, attributes and default values must be preserved, while types are converted according to the list of safe mappings below:

MS Access	MySQL
Text	VARCHAR(n), where n is size of Text column
Memo	TEXT
Byte	TINYINT UNSIGNED
Integer	SMALLINT
Long	INT
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

Migration of data can be verified using visual comparison of random fragments in source and destination tables. Microsoft Access allows to browse the data by double-clicking on table name. MySQL provides two options for the same purpose:

In MySQL console client run the query SELECT * FROM `table name` LIMIT start_record, number_of_records

Highlight the table in the left pane and go to ‘Browse’ tab in phpMyAdmin

Make sure that number of rows in source and destination tables are equal for each table being migrated. MS Access displays the number of rows at the bottom of window containing the data. In MySQL the count of rows can be obtained as follows:

In MySQL console client run the query SELECT COUNT(*) FROM `table name`

In phpMyAdmin highlight table name, click on the ‘Browse’ tab and number of rows will be displayed in the status line at the top of the window

Indexes

To view indexes corresponding to specified table in MS Access, right click on the table name and select ‘Design View’ menu. Then click ‘Indexes’ icon in the toolbar and all necessary information will appear in new window. MySQL provides two options for the same purpose:

In MySQL console client run the query SHOW INDEXES FROM ` table name`

Highlight the table in the left pane, click ‘Structure’ tab and all information about indexes will be listed after the table definition in phpMyAdmin

Relationships between tables

To get information about relationships between tables in MS Access highlight a table, click on “Design” menu item and select “Relationship Report” icon on the toolbar. Then you will see a diagram like on the screenshot below. Double click on a line connecting two tables to get properties of the corresponding relationship:

MySQL provides two options for extracting foreign keys:

In MySQL console client run the query SHOW CREATE TABLE `table name`. Information about foreign keys is at the bottom of the displayed statement.

Highlight the table in the left pane, click ‘Structure’ tab and follow ‘Relations view’ link below the table definition in order to get foreign keys in phpMyAdmin

Queries

In order to validate migration of queries compare SELECT-statements of each Microsoft Access query and the corresponding MySQL view.

MS Access allows to extracts SELECT-statement of the specified query as follows: