Saturday, March 25, 2023
My Blog
HomeTechHow to check MS Access to MySQL migration
Tech

How to check MS Access to MySQL migration

Carry Niery
Carry Niery
posted on

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 

or

  • 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 

or

  • 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` 

or

  • 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`

or

  • 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.

or

  • 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: 

  • Highlight the query, right-click on its name and select “Design View” > “SQL View” option 

MySQL extracts view’s definition via SQL-query: SHOW CREATE VIEW `view name`