Convert MS Access 97 to MySQL 5.5

Convert MS Access 97 to MySQL 5.5

Hi Martha,

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';

Mysqlimport Syntax

C:> mysqlimport –local –fields-terminated-by=,

–fields-enclosed-by='"'

–lines-terminated-by='rn'

–mydb mytable.txt

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:

MyAccess

DBTools

MySQLFront

Debtors are freeware and widely used. You can download from http://www.dbtools.com.br/EN/.

Hope this will help you.