How do I convert from MS-Access to MySQL?
Print Email
Email this article
Close
Email this article
CloseYou are here: Home > Website Support > 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 =
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
Was this information helpful?
Yes NoThank you for your feedback
We are delighted to find that our article resolved your query.
Thank you for your feedback
We will resolve your query as soon as possible.
Please take a few moments to comment on your unresolved query. Simply tell us what your problem is. We guarantee that we'll get back to you within two hours (during office hours) in response to your query.
Related articles
-
Login Now
Our award winning proprietary control panel empowers customers to manage their hosting environment.
Take the Tour
