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) |
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 sqltuned.com by Intelligent Converters.
