MySQL ODBC Drivers

Installation

1. If you are using MS Access 2000, it may be necessary to patch your MS Jet 4.0 database engine before continuing. See the following, for more information.
   • MS Product Support Services: Jet 4.0 Bug Explanation
   • MS Product Support Services: Jet 4.0 Service Pack 5
2. Install the necessary MySQL ODBC driver on your PC. Download the appropriate driver from the MySQL Web site (make sure to get the version that matches the O/S of your PC.
   • http://www.mysql.com/products/myodbc/
3. Configure the MySQL driver for use.

   • Windows 2000

     
     From the Start menu, select Settings then Control Panel. Double click Administrative Tools and then double click Data Sources (ODBC).

   • Windows 95/98/NT

     
     From the Start menu, select Settings then Control Panel. Double-click the ODBC Sources 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.
4. The TDX mysql driver default configuration screen will then appear. You will want to fill out the fields with the appropriate information.
   1. Windows DNS Name - Type a name for this particular driver that you will be using for MySQL. The name is something of your choosing. (example: everyoneMySQL)
   2. Server - This is the name of the Virtual Private Server you will be publishing your database to. (example: myserver.net)
   3. MySQL Database Name - This needs to be the name of the MySQL database you will be connecting to.
   4. User - This needs to be the username for the MySQL database you will be connecting to.
   5. Password - This is the password for the MySQL user in the field above.
   6. Port (if not 3306) - If you are behind a Firewall you will need to open up port 3306 or another port you specify or it will not work correctly.

Exporting an MS Access Database to MySQL

1. Install MySQL on your server. Be aware that you will need to consult the MySQL documentation for information on using MySQL.
2. Add a user with password privileges to your MySQL database. See the following section of the MySQL Reference Manual:
   • User Account Management
3. Open up MS Access and create or select the database you want to move to your server in the Tables section. Once you have selected the appropriate table, select Export... under the File menu.
4. The Save Table screen will appear. You will want to change the Save as type to ODBC Databases and click Export.
5. The Export screen should appear. The Export Addresses to: field should be the name you want to call this specific table on the server.
6. You should now see the Select Data Source screen. Select the Machine Data Source tab and then select the DSN you configured in step 4.
7. The table should then be moved to the Virtual Private Server under the user you specified for MySQL. To verify this, connect to your Virtual Private Server via SSH prompt, as root and find the table. An example of this would be the command:

   % mysql -u myusername new_database_name

Importing a MySQL Database to MS Access

1. In your current database, go to the Tables tab and right-click on the mouse button. This will provide you an option for Import. In the Import screen select ODBC Databases in the Files of type field.
2. This will bring up the Select Data Source screen. Select the Machine Data Source tab and choose the appropriate Data Source Name that you setup previously.
3. The Import Objects screen will appear and you will be given a choice of available tables on the Virtual Private Server that you can choose from. Select the table you want and click OK.