Tiger Technologies: MySQL via ODBC

How do I connect to a MySQL database from my PC using ODBC?

Advanced users running PC software such as Microsoft Access or Microsoft Excel can access their MySQL database (on our servers) over the Internet by using a technology called ODBC. ODBC provides a standard way for programs to access databases. You can install an ODBC driver on your PC which knows how to remotely connect to your MySQL database over the Internet. Many different programs that are compatible with the ODBC interface can then access your database via the MySQL ODBC driver.

In order to do this, you need to install the MySQL ODBC driver on your PC. You can download the driver from the MySQL web site. Follow their instructions for downloading and installing the correct version.

The instructions on this page assume that you are connecting to your MySQL database from a PC running Windows. Versions of the ODBC driver are available for other operating systems, and they should work in a similar fashion.

Security considerations

Accessing your database over the Internet with the MySQL ODBC driver is not inherently secure; all data is sent as plain text. See the bottom of this page for more information about how to secure your connection and protect your data.

You also need to make sure that your database is configured to allow remote connections.

Are there any restrictions on remote connections?

Please keep in mind that in the unlikely event a new security problem in MySQL is discovered, we may be forced to disable all remote connections to MySQL to protect your database. You shouldn't absolutely rely on remote access always being available.

How to connect via ODBC

Download and install the MySQL ODBC driver on your Windows computer.

Once the driver has been installed, follow these instructions to configure an ODBC data source:

1. Open the ODBC Data Source Administrator

The instructions on how to start the ODBC Data Source Administrator vary depending upon the version of Windows you are running:

Windows XP: On the Start menu, click Control Panel. If you are viewing the Control Panel in "Category View", click Performance and Maintenance and then click Administrative Tools; otherwise, if you are viewing the Control Panel in "Classic View", click Administrative Tools. When the Administrative Tools window displays, open Data Sources (ODBC).

Windows Vista: On the Start menu, click Control Panel. Click Additional Options, and then click Data Sources (ODBC).

Windows Server 2003: On the Start menu, choose Administrative Tools, and then click Data Sources (ODBC).

Windows 2000: On the Start menu, choose Settings, and then click Control Panel. Click Administrative Tools, and then click Data Sources (ODBC).

You can edit the settings of an existing data source by selecting it, clicking Configure, and then continuing with step 3 below.

Otherwise, determine whether you want to create a "user DSN" (available only to the currently logged-in user), a "system DSN" (available to any user of the computer), or a "file DSN" (connection settings stored in a file). Click the appropriate tab, then click Add.

2. The Create New Data Source window appears.

Scroll down and choose the MySQL driver, and then click Finish.

3. Configure the data source with the appropriate values.

This is the window where you will configure the data source with the values that will allow your computer to connect to your MySQL database.

Assign a Data Source Name. Other programs running on your PC will use this name to refer to this data source.

Enter a short Description for your own reference.

In the Server field, enter www.example.com.

Enter the MySQL Username of your database. This is the name of your database within MySQL. You can find it on the show settings page for your database within the Tiger Technologies control panel.

Enter the Password for your database. This is the password that you assigned to the MySQL database, not your Tiger Technologies domain account password.

Choose the Database to connect to. Use the same value as the User field. (If you have entered correct values in the Server and User fields, you should be able to click the arrow to drop down the list of available databases and then choose a database from that list.)

You can use the Test button to validate the settings that you have entered.

Click OK when you are done.

Creating and using MySQL tables in Access via ODBC

Within Microsoft Access, you can use an existing MySQL table in your database by opening the File menu, choosing Get External Data, and then clicking Import or Link Tables. Import will make a copy of the MySQL table which can be edited independently from the original MySQL copy. Link Tables will cause the Access database to create a "live link" to the original MySQL table, so there only remains one copy of the data (ie, the copy in MySQL).

You can create a new table in MySQL by using the Export command (on the Access File menu). To do so:

Select a table in your Access database, then open the File menu and choose Export. A window titled Export Table 'TableName' To will appear.
Select ODBC Databases from the Save as type drop-down list at the bottom of the window.
You will be prompted to name the table for MySQL. You will usually want to use the same name as in Access. Either accept the default table name or provide a different name, and then click OK.
Select an ODBC data source that you defined previously.

The MySQL table will be created and its records will be exported from Access into MySQL. (If the table is empty, then the appropriate table will be created on MySQL, but of course it will not have any records in it.)

Access does not have a menu command which lets you create a new table from scratch in MySQL (as opposed to exporting an existing table). The easiest way to create a new MySQL table is to define it within Access and then export it as shown above. You can also use phpMyAdmin to define tables.

Other programs using ODBC to reach your database may be similarly designed.