Application and Software Database Management

Tips to Control MS Access to MYSQL Migration

Written by Albert Headley

It doesn’t is aggravated to change over Microsoft Access to MySQL physically or to utilize an instrument for this reason. It is necessary to watch that all database objects have been changed properly. This article indicates visit manage on this method.

In the first place, it is important to comprehend which thing must be approved in the goal database after the relocation is finished.

Table definitions

Information

Records

Connections between tables (remote keys in MySQL)

Questions (sees in MySQL)

Table definitions

We have found in MS Access all tables are customized in ‘Tables’ hub of the tree see in the left sheet of the entrance. When we Right tap on the table, it characterizes you have to find and choose ‘Structure View’ from the menu thing. On that side, you have seen the window containing meanings of every single table section. MySQL permits seeing table definition as pursues:

In MySQL comfort customer runs SQL articulation DESC table_name;

In phpMyAdmin feature the table in the left sheet and go to ‘Structure’ tab

Set up to watch that table definitions are changed over effectively, you need to look at every section definition in MS Access and MySQL tables. Type, estimate What’s more default regard for each ms get area must make converters under those appropriate relative over MySQL as expressed by this table:

MS Access MySQL

Text VARCHAR(n), where n is size of Text segment

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 section’s semantic

Indeed/No BIT(1) or BOOL

Ole Object LONGBLOB

Hyperlink VARCHAR(255)

Replication ID (guid) VARCHAR(38)

Picture result for MS Access to MySQL Migration

Information

Precision of information transformation can be approved by visual correlation of a specific part from MS Access and MySQL tables. MS Access permits perusing a table by double tap on its name in the left sheet. In MySQL you can do likewise as pursues:

In mysql support customer simply run SQL proclamation SELECT * FROM table_name LIMIT start_record, number_of_records

In phpMyAdmin, feature the table present in the left most sheet and after that go to ‘Peruse’ tab

Likewise, it will be important to watch that the sum total of what records have been changed by looking at the check of lines for each table in source and goal databases. Microsoft get to gets the sum from guaranteeing records at those most minimal piece of the table checking window. MySQL grants to get the quantity of lines for a table in the accompanying way:.

In MySQL support customer run SQL articulation SELECT COUNT(*) FROM table_name;

In phpMyAdmin feature, the table present in the left sheet, go to ‘Peruse’ tab and number of records will be shown in the status line “Indicating lines … (number_of_records absolute … )” at the highest point of the window

Lists

Microsoft Access permits perusing lists of the table in ‘Structure View’ (see ‘Table Definitions’ areas above for insights concerning this alternative). When you click ‘Record’ catch in MS Access toolbar and distinctive sorts of the file will show another window with all characteristics.

In MySQL you can list lists as pursues:

In MySQL comfort customer just runs SQL explanation SHOW INDEXES FROM table_name;

In phpMyAdmin underscore the table in the left side, go to ‘Structure’ tab and all files will be recorded directly after table structure

Connections between tables (outside keys in MySQL)

In Microsoft Access all connections between tables can be seen as graphs. Feature a table in the left sheet, go to “Structure” menu and snap “Relationship Report” catch in the toolbar. Double tap on any relationship pointer (line between two tables) to see its properties:

MySQL permits seeing data about outside keys as pursues:

In MySQL comfort customer run SQL explanation SHOW CREATE ‘table name’

In phpMyAdmin feature, the table in the left sheet, go to ‘Structure’ tab and snap ‘Relations see’ interface beneath the table definition

Inquiries (sees in MySQL)

The initial step is the point at which you see MS Access inquiries as SQL SELECT-proclamations. You can approach it by the accompanying advances:

Open the question in configuration see utilizing the “Structure View” catch or right-tap on the inquiry and select “Plan View” menu thing

Select “View” from the menu bar and select “SQL View” alternative

Unfortunately, there is no such method to watch that inquiries were changed fittingly close to manual looking at SELECT-explanations of every MS Access question and comparing MySQL see. This sort of errand requires profound proficiencies in both Microsoft Access and MySQL vernaculars of SQL.

On the off chance that you feel above methodology excessively confused or dull, the magnificent path is to utilize dependable relocation apparatus which improves your undertaking. The dependable instrument exchanges MS get to database to MySQL server. The one that emerges of these apparatuses is MS Access to MySQL by Intelligent Converters.

About the author

Albert Headley

Albert Headley was a PR and Creative Editor of a renowned big Tech Company. But following his passion and ambition for writing and technologies, he gave up his job and started this blog. He is now a big name in the technology and blog business.