How to Control Results of MS Access to MySQL Migration

Published On December 2, 2016 | By Lisa Patterson | Database

It doesn’t is disturbed to convert Microsoft Access to MySQL manually or to use an instrument for this purpose. It is compulsory to check that all database objects have been transformed appropriately. This article shows frequent guide on this procedure.

 In the beginning, it is necessary to understand which item must be validated in the destination database after the migration is complete.

  • Table definitions
  • Data
  • Indexes
  • Relationships between tables (foreign keys in MySQL)
  • Queries (views in MySQL)

Table definitions

We have seen in MS Access all tables are programmed in ‘Tables’ node of the tree view in the left pane of the access.  When we Right click on the table, it defines you need to discover and select ‘Design View’ from the menu item. On that side, you have seen the window containing definitions of all table columns. MySQL allows viewing table definition as follows:

  • In MySQL console client runs SQL statement DESC table_name;
  • In phpMyAdmin highlight the table in the left pane and go to ‘Structure’ tab

In place to check that table definitions are changed over correctly, you have to compare each column definition in MS Access and MySQL tables. Type, size What’s more default esteem for every ms get section must make converters under those proper proportional over MySQL as stated by this table:

MS Access MySQL
Text VARCHAR(n), where n is size of Text column
Memo TEXT
Byte TINYINT UNSIGNED
Integer SMALLINT
Long INT
Memo TEXT
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)

Image result for MS Access to MySQL Migration

Data

Accurateness of data conversion can be validated by visual comparison of a certain fragment from MS Access and MySQL tables. MS Access allows browsing a table by double-click on its name in the left pane. In MySQL you can do the same as follows:

  • In mysql console client just run SQL statement SELECT * FROM table_name LIMIT start_record, number_of_records
  • In phpMyAdmin, highlight the table present in the left most pane and then go to ‘Browse’ tab

Also, it will be necessary to check that all records have been changed by comparing the count of rows for each table in source and destination databases.  Microsoft access gets the amount from claiming records at those lowest part of the table scanning window. MySQL permits to get the number of rows for a table in the following way:.

  • In MySQL console client run SQL statement SELECT COUNT(*) FROM table_name;
  • In phpMyAdmin highlight, the table present in the left pane, go to ‘Browse’ tab and number of records will be displayed in the status line “Showing rows … (number_of_records total …)” at the top of the window

Indexes

Microsoft Access allows browsing indexes of the table in ‘Design View’ (see ‘Table Definitions’ sections above for details about this option). When you click ‘Index’ button in MS Access toolbar and different types of the index will be displaying a new window with all attributes.

In MySQL you can list indexes as follows:

  • In MySQL console client just runs SQL statement SHOW INDEXES FROM table_name;
  • In phpMyAdmin emphasize the table in the left side, go to ‘Structure’ tab and all indexes will be listed right after table structure

Relationships between tables (foreign keys in MySQL)

In Microsoft Access all relationships between tables can be viewed as diagrams. Highlight a table in the left pane, go to “Design” menu and click “Relationship Report” button in the toolbar. Double click on any relationship indicator (line between two tables) to view its properties:

MySQL allows viewing information about foreign keys as follows:

  • In MySQL console client run SQL statement SHOW CREATE TABLE `table name`
  • In phpMyAdmin highlight, the table in the left pane, go to ‘Structure’ tab and click ‘Relations view’ link below the table definition

Queries (views in MySQL)

The first step is when you see MS Access queries in the form of SQL SELECT-statements. You can approach it by the following steps:

  • Open the query in design view using the “Design View” button or right-click on the query and select “Design View” menu item
  • Select “View” from the menu bar and select “SQL View” option

Regrettably, there is no such way to check that queries were transformed appropriately beside manual comparing SELECT-statements of each MS Access query and corresponding MySQL view. This type of task requires deep knowledges in both Microsoft Access and MySQL dialects of SQL.

If you feel above procedure too complicated or tedious, the excellent way is to use reliable migration tool which simplifies your task. The reliable tool transfers MS access database to MySQL server.  The one that stands out of these tools is MS Access to MySQL by Intelligent Converters.

Like this Article? Share it!

About The Author

Comments are closed.