How To Validate MS Access To MySQL Migration?

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` 

A blogger with a zeal for learning technology. Enchanted to connect with wonderful people like you.
Exit mobile version