One of the most important steps in database migration is checkingeverythinghas 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 newwindow 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 valuesare preserved, while data types are converted from MS Access to MySQLaccording 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:
- throughthe 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 phpMyAdminwindow
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 provides two options for the same purpose:
- throughthe query SELECT * FROM `table_name` LIMIT start_record, number_of_recordsin 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’ iconappears in the toolbarthen and it leads to all necessary information about indexes appearing in the separate window.MySQL provides two options for the same purpose:
- through the querySHOW INDEXES FROM ` table name`in command line client
or
- Highlight the appropriate item in tablestree 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
- Thenselect “SQL View” option in 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`
Find more information about MS Access to MySQL migration at: https://www.convert-in.com/docs/acc2sql/intro.htm
