One of the most important steps in database migration is checking everything has been migrated properly. This article explains how to implement the procedure.
Table definitions
Conversion of table definitions can be validated by comparison of every column in MS Access and MySQL tables. Microsoft Access displays all tables as tree-view in left pane of the main window. Right click on particular table name and select ‘Design View’ menu item. Then new window will be opened with all columns and related properties.
MySQL provides two options to browse the table structure:
- Through the query DESC `table_name` from command line client
or
- Highlight the table in the corresponding tree view and navigate to ‘Structure’ tab in phpMyAdmin
Correct conversion of the table definition means that size, attributes and default values are preserved, while data types are converted from MS Access to 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 |
| 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
First step of validating data migration is to check that all rows have been transferred from MS Access to MySQL by comparison number of rows in source and destination tables. Microsoft Access prints it at the bottom of data window. MySQL provides two options to obtain the number of rows:
- through the query SELECT COUNT(*) FROM `table_name` in command line client
or
- Highlight the table in the corresponding tree view, navigate to the ‘Browse’ tab and number of rows will be displayed in the status line at the top of the phpMyAdmin window
Data itself can be verified through comparison of random fragments in source and destination tables. Microsoft Access exposes the table data by double-clicking on the corresponding table name in left pane. MySQL allows to do the same through any of these two options:
- through the query SELECT * FROM `table_name` LIMIT start_record, number_of_records in command line client
or
- Highlight the corresponding item in tree-view on the left pane and go to ‘Browse’ tab in phpMyAdmin
Indexes
MS Access exposes indexes corresponding to specified table through right clicking on the table name and navigating to ‘Design View’ menu item. ‘Indexes’ icon appears in the toolbar then and it leads to all necessary information about indexes appearing in the separate window. In MySQL there are two options for listing indexes:
- through the query SHOW INDEXES FROM ` table name` in command line client
or
- Highlight the appropriate item in tables tree view, navigate to ‘Structure’ tab and all information about indexes can be found next to the table structure in phpMyAdmin
Queries
Migration of queries from MS Access to MySQL can be validating through comparison of SELECT-statements of each Microsoft Access query and the corresponding MySQL view.
Those SELECT-statements can be extracted in Microsoft Access as follows:
- Right-click on the query in tree view and select “Design View” menu item
- Then navigate to “SQL View” entry of the “View” section
MySQL allows to extract view’s definition in phpMyAdmin and command line clients through the following SQL-query: SHOW CREATE VIEW `view name`
