How to convert from MS-Access to MySQL
Convert a database from MS-Access to MySQL:
- Create an ODBC data source which points to a MySQL database on a machine containing the Access database.
- In order to create an ODBC data source which points to a MySQL database, you will first need to download and install the MySQL driver (available from http://www.mysql.com).
- Once you have completed the download, install the MySQL ODBC driver by executing the file.
- The next step is to create a new ODBC File DSN. Click the Start button, point to Settings, and then click Control Panel. Open the Administrative Tools and double-click Data Source (ODBC).
- Click on the File DSN tab, and then click Add. Choose MySQL ODBC and click next.
- Type a name for the connection – for example the name of your database. Click Finish. The last screen would require you to enter all of the supplied MySQL details.
- Open the Microsoft Access database, right click each table and click “Export”.
- In the “Save as type” field, select ODBC Databases from the dropdown list.
- Accept / Edit the table name (Note: table names will be case sensitive on our servers).
- Select the newly created datasource as the Export target.
- The Export to the ODBC datasource will create the tables’ definitions and populate them with the data from Access.
Other data definitions will need to be done manually, for example:
- Primary keys and indexes (in MySQL, a primary key can only be defined on columns which are specified as “not null”, so this may need to be done as well).
- Auto-number fields (it is recommended that the SQL for the manual changes above are placed into a script file for future speedy execution if needed). An example of ASP code for setting up the new connection string, could be as follows:
Dim cn set cn =
Database=database_name; UID=user_name; PWD=password"
(A file dsn can also be used)
Downloadable programs are also available to migrate MS-Access databases to a MySQL server.
An example of such program (freeware) is available at: http://www.bullzip.com/products/a2m/info.php.
ASP Documentation: http://docs.sun.com/app/docs/doc/817-2514-10