How do I work with an MS access database when using MySQL?

How do I work with an MS access database when using MySQL?

This answer explains how you can make use of MS Access, while still using MySQL.

Converting an MS Access database to MySQL

Converting a Microsoft Access database to MySQL requires you to have MySQL ODBC drivers to connect to MySQL running on your hosting account and import/export databases to and from your own PC.

  • On your own PC, download the appropriate MyODBC driver from www.mysql.com
  • Unzip the driver and then run the setup program for the driver.

You will then need to configure the MySQL driver for use as follows:

Windows XP:- Click "Start" and then click "Control Panel". Double click "Administrative Tools" and then double click "Data Sources (ODBC)".

Windows 2000:- Click "Start", point to "Settings", and then click "Control Panel". Double click "Administrative Tools" and then double click "Data Sources (ODBC)".

Windows 95/98/NT:-Go to your "Control Panel" (start -> settings -> control panel) and double click on "ODBC icon.

You will then need to select whether you want to configure the ODBC driver for use by a single user or for use by every user on the computer. The first tab, "User DSN" is for only a specific user and can only be used on your specific computer. The second tab, "System DSN" is used to configure the ODBC driver for all users on your computer. Depending on which you choose to use, you will then click the add button on the right side. By clicking the add button you will be given a choice of drivers you can set up for a data source. You should find MySQL in the list. Select MySQL and click finish.

The TDX MySQL driver default configuration screen will then appear. You will want to fill out the fields with the appropriate information as follows:

  • Windows DSN Name - Choose a name for this particular driver that you will be using for MySQL, for example: everyoneMySQL.
  • Server - the domain name of the hosting account to which you will be publishing your database, for example: myserver.net.
  • MySQL Database Name - the name of the MySQL database to which you will be connecting.
  • User - the username for the MySQL database to which you will be connecting.
  • Password - The password, if applicable for the MySQL user in the field above.
  • Port (if not 3306) - If you are behind a Firewall you will need to open up port 3306 or another specified port of your choice for the process to work.

Exporting a Microsoft Access Database to MySQL

  • Open Access and, in the "Tables" section, create/select the database you would like to export to your hosting account. Then, select "Save As/Export" from the "File" menu, and choose the "To an External File or Database" option.
  • In the "Save Table" screen which you should then see, select the field and change the "Save as type" to "ODBC Databases". Click "Export".
  • In the "Export Addresses to:" field of the Export screen which will then appear, enter a name of your choice for this database table on your hosting account.
  • In the "Select Data Source" screen you will then see, select the "Machine Data Source" tab and choose the relevant Data Source Name.

Importing a MySQL Database to Microsoft Access

  • In the Tables tab of Access, right-click on the mouse button. You should now see an "Import" option. In the "files of type" field of the Import screen, choose "ODBC".
  • In the "Select Data Source" screen you can now see, choose the "Machine Data Source" tab and select the appropriate "Data Source Name".
  • The "Import Objects" screen will appear and you will be given a choice of available tables on the Virtual Server that you can choose from. Select the table you want and click "OK".

Tip: MyODBC and an ODBC database - You can tie-in a database written in a PC based database package that is ODBC compliant - such as Microsoft Access, FoxBase and Dbase Pro. To do so, you will require drivers that convert the communication - you can download these drivers for Windows 95/98/ME and Windows NT/2000 from http://www.mysql.com/downloads/api-myodbc.html

The settings you will need are:

  • Hostname (Your domain name)
  • MySQL database name (the name of the database)
  • Username (database username)
  • Password (database password)
  • Port - 3306


Please note: If you are connecting via a script on the server, use "cust-mysql-us-01" as the server name. If you are connecting from your own PC, use the IP address for your server. You should find this in your welcome email.