I have not tried this myself, but this is what I would have tried after doing a quick RTFM in the MySQL PDF manual, section 3.3.3, page 175.
First export your Access database tables with the Export function in MS Access. Export each table as plain text with tab separation.
All NULL values must be replaced with a \N in the exportet text file.
Use the MySQL command line client to create the new database and all tables.
When I create new databases I usually do a script so I can elaborate easy.
Script Example:
DROP DATABASE Test;
CREATE DATABASE Test;
USE Test;
CREATE TABLE mailinglist (
ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
Email VARCHAR(30),
FirstName VARCHAR(15),
LastName VARCHAR(15)
);
SHOW COLUMNS FROM mailinglist;
GRANT ALL ON Test.* to WEB_USER@localhost;
The script first deletes the previous database attempt so the next one will be fresh (DROP...).
Then the database is created again (empty) (CREATE...).
Then I switch to the new database (USE...).
The use CREATE TABLE for each table you need.
Then I check the table to se that all fields (columns) are correct with SHOW COLUMNS FROM .....
When everything is done I grant access to the entire database from the PHP scripts, where WEB_USER is the user name used in the mysql_connect(); function, and @ is the server running the MySQL server.
Run the script from the MySQL command line client:
mysql> source <filename>
When the database looks ready go on with importing your data
by using the MySQL command line client to import each table using the command LOAD DATA LOCAL INFILE <filename> INTO TABLE <table>;
Now the database should be imported.
I have not tested to import anything yet but this is the way I would have tried it.
Let me know if it works, good luck.
// Max
