Working with MySQL Connector/ODBC & Office

by John Collins

ODBC (Object Database Connectivity) is a widely used database API (Application Programmers Interface) used for establishing brand-independent database connections. Assuming a database has a driver available for ODBC, that driver will act like a broker between the ODBC client requesting some information and the database, making it possible to get all kinds of disparate systems connected to each other via the ODBC service.

MySQL Connector/ODBC is the ODBC driver available for the MySQL database, and can be downloaded freely from MySQL.com. In this tutorial, I will show you how you can use MS Office applications to connect via ODBC to a MySQL database, then download data from that source to the application in use (here I will demo Excel and Access).

The software I will be using are the following, if you are using different versions of Windows or Office, then your interface might differ slightly (but hopefully not too much!):

• Windows 2000 Professional, Service Pack 3: this should come with Data Sources (ODBC) installed by default.
• Office 2003 Professional: As of writing, this is the latest version of Office, but an older version should work just fine.
• MySQL 4.1.3 Beta: This is the latest test release of 4.1x available, but again an older version should be fine. On my test network, I have MySQL installed on a separate machine running on the Redhat 9 Linux operating system, but this could just as easily be running on Windows.
• MySQL Connector/ODBC 3.51: The latest production version of the MySQL ODBC driver.

The Different Roles of the MS Office User and the MySQL DBA

It is worth pointing out that in a real environment, it is unlikely that the person using Office to access the data is also the DBA (Database Administrator) for the MySQL database. Generally, the MySQL database will reside on a remote server, while the ODBC service and the MySQL connection driver will be installed locally on a Windows workstation.

ODBC connections present a convenient method for non-database specialists to access live data, without having to request that data on a case-by-case basis from DBAs. While this will free up some work for the DBA, it is important that the DBA realises that ODBC requires a user account to access the database, so an account will have to be set up. Generally this account will only require read-only access to a limited set of tables within a single database, and therefore it is important that this is the only access granted, and that no other data is viewable to the ODBC user for security reasons.

Granting table privileges from the MySQL console is beyond the scope of this tutorial, so I will assume that a DBA has set up this account correctly. In my test environment, the MySQL database and ODBC client are on separate machines that are networked together: redhat.soylent contains the MySQL database running on Linux; while w2k2.soylent is acting as the Windows 2000 client with the MySQL Connector/ODBC driver installed. For simplicity I will use the root account and a sample password (but you should never do this in a live environment for security reasons, as the root user can see everything on the database!).

Installing MySQL Connector/ODBC 3.51

We will begin by installing the MySQL Connector/ODBC package on w2k2.soylent. After downloading the Windows executable file, double click on it and follow the onscreen instructions. It is quite safe to choose all of the default options for this simple install. If you install it successfully, you should see a confirmation screen similar to the one below:

Setting up a DSN in ODBC

Now that we have the correct driver installed, we need to set up an ODBC DSN (Data Source Name) on our Windows client w2k2.soylent. A DSN is effectively a "name" that any ODBC-compliant software can use to access the database connection detailed within the DSN. As you might expect, because a DSN is a database connection, we will need to provide it with a valid username and password to access that database:

1. Start -> Settings -> Control Panel -> Administrative Tools
2. If ODBC is installed, there should be an icon here called Data Sources (ODBC). Double-click this.
3. Choose the System DSN tab (a System DSN is available to all users of a computer), then click Add...
4. Scroll down until you find MySQL ODBC Driver 3.51 in the list, choose it, then click Finish
   
   
   
5. Now fill in the data source name, server name, database name, username and password for your server, like in the screen below:
   
   
   
6. You can click the Test Data Source button to check to see if the connection works.

If your DSN is able to connect, then click ok and exit the Data Sources dialog in the Control Panel.