How To Convert From MS-Access To MySQL

1. Create an ODBC data source which points to a MySQL database on a machine containing the Access database.
2. 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).
3. Once you have completed the download, install the MySQL ODBC driver by executing the file.
4. 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).
5. Click on the File DSN tab, and then click Add. Choose MySQL ODBC and click next.
6. 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.
7. Open the Microsoft Access database, right click each table and click “Export”.
8. In the “Save as type” field, select ODBC Databases from the dropdown list.
9. Accept / Edit the table name (Note: table names will be case sensitive on our servers).
10. Select the newly created datasource as the Export target.
11. The Export to the ODBC datasource will create the tables’ definitions and populate them with the data from Access.

• 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 =

Server.CreateObject("ADODB.Connection")cn.connectionstring

= "Driver={Mysql};Server=sql_server_name;

Database=database_name; UID=user_name; PWD=password"

(A file dsn can also be used)

Note:

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.

Related links:

ASP Documentation: http://docs.sun.com/app/docs/doc/817-2514-10