N/APosted on - 03/04/2013
I have a huge database in MS Access 97, I am redesigning the application and want to convert all my data to MySQL 5.5. Is there any tool available so that I can convert Access 97 to MySQL 5.5 without losing any existing data? Please help me about this.
Convert MS Access 97 to MySQL 5.5
There are 3 ways for migrating data from MSACCESS97 to Mysql5.
1) Telling Microsoft Access to Export Its Own Tables to txt file or csv file and then import the csv file or txt file to mysql5.
We will use the export feature provided by the MSaccess and exports the data into a csv file or txt file and then use the sqlimport or load command to load the data into mysql tables.
Load data syntax
mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
-> INTO TABLE mytable
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-> LINES TERMINATED BY 'rn';
C:> mysqlimport –local –fields-terminated-by=,
The advantage of this approach is there is no direct connection required between the Access machine and Mysql server.
2) Using Converters That Generate Intermediate Files
In this approach converter is used to transfer data from MSAccess to one or more . sql files.These .sql files contain statements/query to create tables ,insert data into table etc.. Then these SQL files are run in mysql5 .There are several free converters available like exportsql.txt,access_to_mysql.txt,mdb2sql.bas
3) Converters That Perform Direct Data Transfer
Some conversion tools can transfer data directly from an Access database into MySQL. That is, they create the MySQL tables for you and load the information into them as well. This avoids the need for any intermediate files. On the other hand, such tools require that you are able to connect to the MySQL server from the machine on which your Access information is stored.
Tools that can perform direct data transfer are:
Debtors are freeware and widely used. You can download from http://www.dbtools.com.br/EN/.
Hope this will help you.