admin June 22, 2018
How to Check MS Access to MySQL Migration

After database migration is completed, the person responsible for the process must check that everythinghas been migrated properly. This whitepaperexplainshow 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?Beloware 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 exploreall tables inform of tree-view. Right click on the table name and select ‘Design View’ menu. This will force openingnewwindow withlist all table columns and related properties. In MySQL there are two options to explore table structure:

  • Run the queryDESC `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 validatedby comparison ofcolumndefinitions in MS Access and MySQL tables. Size, attributes and default valuesmust be preserved, while types are convertedaccording to thelist 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 verifiedusing visual comparison of random fragmentsin 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 `tablename`

or

  • In phpMyAdminhighlight 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 querySHOW INDEXES FROM `tablename`

or

  • Highlight the table in the left pane, click ‘Structure’ tab and all information about indexes will be listed after the table definitionin phpMyAdmin

Relationships between tables

To get information about relationships between tablesin 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 aline connecting two tables to getproperties of the corresponding relationship:

MySQL provides two options for extracting foreign keys:

  • In MySQL console client run the querySHOW 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 orderto validatemigrationof queriescompare SELECT-statements of each Microsoft Access query and the corresponding MySQL view.

MS Access allows to extractsSELECT-statement of the specified queryas follows:

  • Right-click on the query and select “Design View” menu item
  • Select “View” from the menu and select “SQL View” option

MySQL extractsview’sdefinition via 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

Leave a comment.

Your email address will not be published. Required fields are marked*