There are a number of ways to approach this.
One approach is to use the export feature provided by Access itself to write out the contents of each table as a text file. Each file then can be loaded into MySQL using a LOAD DATA statement or the mysqlimport command-line utility. Suppose you export a table mytable into a file mytable.txt using CSV (comma separated values) format, and you want to import it into a table named mytable in a MySQL database named mydb. You can invoke the mysql program, then issue a LOAD DATA statement to import the file like this:
-
C:\> mysql mydb
-
mysql> LOAD DATA LOCAL INFILE 'mytable.txt'
-
-> INTO TABLE mytable
-
-> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
-
-> LINES TERMINATED BY '\r\n';
Or you can use mysqlimport from the command line (type the command all on one line):
-
C:\> mysqlimport --local --fields-terminated-by=,
-
--fields-enclosed-by='"'
-
--lines-terminated-by='\r\n'
-
mydb mytable.txt
If you need to provide connection parameters such as the hostname, user name, or password, list them on the mysql or mysqlimport command line before the database name:
-
C:\> mysqlimport --local --fields-terminated-by=,
-
--fields-enclosed-by='"'
-
--lines-terminated-by='\r\n'
-
-h some_host -p -u some_user
-
mydb mytable.txt
The MySQL tables must already exist before you can load data into them, so you must issue the appropriate CREATE TABLE statements yourself.
Alternatively there are a number of tools you can use to import Access into MySQL.
DBTools Access2MySQL BullZip MS Access to MySQL
Mary