How to Control Results of MS Access to MySQL Migration
|[Access to MySQL Converter] [Types Mapping] [Converting Queries]|
First, it is necessary to understand which objects must be validated in the destination database after migration is complete:
In MS Access all tables are listed in 'Tables' node of the treeview in the left pane. Right click on the table which definition you need to explore and select 'Design View' menu item. Then you will see the window containing definitions of all table columns. MySQL allows viewing table definition as follows:
In order to check that table definitions are converted properly, you have to compare each column definition in MS Access and MySQL tables. Type, size and default value of each MS Access column must be converters into the appropriate equivalent in MySQL according to this table:
|Text||VARCHAR(n), where n is size of Text column|
|Date/Time||DATE or TIME or DATETIME depending of column's semantic|
|Yes/No||BIT(1) or BOOL|
|Replication ID (guid)||VARCHAR(38)|
Accuracy of data conversion can be validated by visual comparison of 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:
SELECT * FROM table_name LIMIT start_record, number_of_records
Also, it is necessary to verify that all records have been converted by comparing count of rows for each table in source and destination databases. Microsoft Access indicates number of records at the bottom of the table browsing window. MySQL allows to get count of rows in a table in the following way:
Showing rows ... (number_of_records total ...)at the top of the window
MS Access allows to browse indexes of the table in 'Design View' (see 'Table Definitions' sections above for details about this option). Click 'Indexes' button in MS Access toolbar and indexes will be displayed in new window with all attributes.
In MySQL you can list indexes as follows:
SHOW INDEXES FROM table_name;
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 to view information about foreign keys as follows:
SHOW CREATE TABLE `table name`
The first step is to explore MS Access queries in the form of SQL SELECT-statements. You can approach it by the following steps:
MySQL can extract information about views using this query:
SHOW FULL TABLES IN database_name WHERE TABLE_TYPE LIKE 'VIEW';
Unfortunately, there is no other way to check that queries were converted properly besides manual comparing SELECT-statements of each MS Access query and corresponding MySQL view. The task requires deep knowledges in both Microsoft Access and MySQL dialects of SQL.