|Published on 2004-10-01 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!):
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!).
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:
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:
If your DSN is able to connect, then click ok and exit the Data Sources dialog in the Control Panel.
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.
To import the track_listing table into Excel 2003, do the following steps:
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:
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.