Introduction to ODBC and MySQL Connector ODBC

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 realizes 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.

Connecting to MySQL from MS Office

As we have our system-wide DSN set up and connecting to the MySQL server, it is time to test it in a real application. Here I will be using Excel and Access to read from the test database that ships with MySQL. I have created a test table in that database called track_listing, which has two columns and five records. It is this data that we will be importing into these MS Office applications.

MS Excel

To import the track_listing table into Excel 2003, do the following steps:

1. Start Excel with a blank spreadsheet.
2. Choose Data -> Import External Data -> Import Data
3. In the dialog box that appears, click the New Source button
4. Choose ODBC DSN, then choose the name of your DSN that you set up earlier:
5. Choose the database and table you want, you will then be asked to save your connection in a .odc file. Do so.
6. You will now be brought back to the Select Data Source... dialog, with your new .odc file in the file name field. Click Open to open this connection.
7. Click ok to insert the data into the existing work sheet in Excel. If your import worked correctly, you should now see the data from the MySQL table in your Excel spread sheet, as demonstrated below:

MS Access

Now onto Access, which is itself a database system, therefore it has more in common with MySQL than Excel does (the main difference being that Access is a file-based database, while MySQL is server-based). To import the track_listing table into Access, carry out the following steps:

1. Start Access, and create a new blank database on your hard drive.
2. Choose File -> Get External Data -> Import...
3. In the file type drop-down, choose ODBC Databases
4. Now is this new dialog box, click the Machine Data Source tab, and choose your DSN from the list.
5. Access will then display a list of tables from the database. Choose the one you want.
6. Access will now create a new table in the Access database for you, containing the field names and data records from your MySQL database:

        

Conclusion

MySQL is a fine database, with rock-solid dependability and very fast query response times. However, for those users that do not possess a degree in computer science, it is not exactly the most user-friendly database around. Furthermore, MySQL has no built-in reporting tools, something that the MySQL designers leave to the software developers who build systems around MySQL to worry about.

The MySQL Connector ODBC driver admirably bridges this gap to allow non-technical users to access their data with a set of tools that they tend to be much more comfortable with; Microsoft Office. Furthermore, the report building tools of Access and the chart building wizards of Excel are ideal for building professional reports based on MySQL stored data in no time, with the bare minimum of training required.

ODBC is a solid technology that has been around for a long time now, and it is one that is likely to remain in future releases of Windows due to its wide user base. As long as the company behind the MySQL database, MySQL AB, maintain the MySQL Connector ODBC project, then an increasing amount of users are likely to migrate to this package due to its ease of use and implementation.

---

Updated 2020 : note that the above post is out-of-date, given this post was originally published in 2004, but is left here for archival purposes.