No matter whether Microsoft Access database is migrated to MySQL manually or automated using dedicated software like Access to MySQL converter, it is very important to verify that all entries are transferred properly. First step of this task is to find out what database objects must be validated. All the primary objects to verify are listed below:
- Table definitions
- Data
- Indexes and constraints
- Relationships between tables (foreign keys in MySQL)
- Queries (views in MySQL)
Table definitions
In Microsoft Access main window right click on the table name and select ‘Design View’ menu. This will open new window containing all table columns and the corresponding attributes. MySQL provides two options to view table definitions:
- In the standard command line client run the statement DESC `table name`
or
- In phpMyAdmin highlight table name in the left pane and proceed to the ‘Structure’ tab
MS Access table is migrated to MySQL properly if every column has equal size and default value compared to the resulting table and 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
There are number of checks to validate that the data was migrated property:
- Make sure that number of rows in MS Access and MySQL tables are equal. MS Access displays the number of rows at the bottom of window containing the data. In MySQL the count of rows can be obtained using the query SELECT COUNT(*) FROM `table name`
- Calculate the sum of numerical columns in MySQL table and compare with the same in the MS Access.
- Check max size of BLOB data in both source and destination databases
Indexes
First, make sure you cannot enter duplicate values for every primary key and unique index. Then you can compare count of foreign keys in Microsoft Access and MySQL databases, indexed column and the corresponding properties.
To view indexes that belong to particular MS Access table, right click on the table name and select ‘Design View’ menu. Then click ‘Indexes’ icon in the toolbar to view all necessary information about indexes in new window. MySQL provides two options for the same purpose:
- In the standard command line client run the query SHOW INDEXES FROM ` table name`
or
- In phpMyAdmin highlight the table name in the left pane, click ‘Structure’ tab and all information about indexes will be listed at the bottom of table definition
Relationships between tables
First, make sure that foreign keys prevent you from entering inconsistent data. Then you can compare number of foreign keys in source and destination databases, column lists and the corresponding properties.
MS Access allows to explore relationships between tables by highlighting the table name, clicking on “Design” menu and selecting “Relationship Report” icon on the toolbar. This will make relationships diagram appear in new window. Double click on a line connecting two tables to get properties of the corresponding relationship as illustrated below:
MySQL provides two options for extracting foreign keys:
- In the standard MySQL console client run the statement SHOW CREATE TABLE `table name`. Foreign keys that belong to the specified table will be listed at the bottom of table definition.
or
- In phpMyAdmin highlight table name in the left pane, click ‘Structure’ tab and follow ‘Relations view’ link below the table definition to list all foreign keys that belong to the specified table.
Queries
In order to validate migration of queries you have to compare SELECT-statements of each Microsoft Access query and the corresponding MySQL view respecting syntax differences of the two DBMS.
MS Access allows to view code of the query via following steps:
- Right-click on the query name in the left pane and select “Design View” menu
- Select “View” item of the pop-up menu and then select “SQL View” option
MySQL extracts source code of the view using SQL statement: SHOW CREATE VIEW `view name`
