Open Database Connectivity, or ODBC for short, is a standard application programming interface (API) for accessing database management systems (DBMSs). Let’s break this definition down to make it simpler.

First, an API is a software intermediary that lets two applications communicate. Think of it as a middleman, like a real estate agent that brokers a deal between a property owner and a prospective buyer.

Next, a DBMS is a program that stores, retrieves, and runs data queries. It is a link between an end-user and a database that lets the user create, read, update, and delete data from a database.

So, simply put, ODBC serves as a mediator between a program like Microsoft Excel and a data source like a comma-separated values (CSV) file stored in a SQL database. It will let an end-user access and use the CSV file via Excel on his or her computer.

Contents

Read More about Open Database Connectivity

Here’s a little trivia.

Did you know that Microsoft and Simba Technologies initially developed ODBC in the early 1990s? In fact, it served as the basis of the Call Level Interface (CLI) standardized by the SQL Access Group for the Unix and mainframe fields.

When Was Open Database Connectivity Created?

As mentioned above, Microsoft introduced the ODBC standard in 1992. It was designed to allow users to access SQL databases in a unified way. That means no matter what compatible program an end-user chooses to use to view and edit a SQL database, he or she would be able to do so.

Over time, Microsoft introduced OLE DB, a broader data access standard that went beyond SQL databases. It enabled users to access any data source so long as it was in tabular format.

Today, the ODBC standard is more common than OLE DB since it is more widely supported even by Oracle and IBM. It has thus become a cross-platform data access standard.

How Does Open Database Connectivity Work?

For ODBC to work, you will need the following components:

  • ODBC-enabled application: Any ODBC-compatible application. Examples include Microsoft Excel, Tableau, Crystal Reports, Microsoft Power BI, or any similar application (e.g., spreadsheet, word processor, data access and retrieval tool, etc.). This application passes SQL statements to and receives results from the ODBC driver manager.
  • ODBC driver manager: It loads and unloads ODBC drivers on behalf of an application. Windows, for instance, comes with a default driver manager that processes ODBC function calls or passes them to an ODBC driver and resolves ODBC version conflicts.
  • ODBC driver: It processes ODBC function calls, submits SQL requests to a specific data source, and returns results to the application. It may also modify a program request to conform to syntax supported by the associated database.
  • Data source: Simply put, it is the source of information. It can be a file, a particular database on a DBMS, or even a live data feed. It can be located on the same computer as the program or another network-connected computer.

So, how do all these components work together?

Let’s say an end-user needs to obtain customer data from a database stored on the company’s DBMS. First, the user needs to open an ODBC-compatible application like Microsoft Excel. When the user accesses the DBMS, locates the data source he or she needs, and then opens it, Excel automatically talks to the ODBC driver manager, who looks for the necessary ODBC driver to open the file. Once all that is done, the user can now view and retrieve the information he or she needs.

Here’s a diagram that shows the process.

how open database connectivity (ODBC) works

—

All organizations that use applications to access information in relational databases need ODBC—a standard that lets the compatible programs work with any database. You can thus think of ODBC as a universal translator between applications and data sources.

Key Takeaways

  • Open Database Connectivity, or ODBC for short, is a standard API for accessing DBMSs.
  • Microsoft and Simba Technologies developed ODBC in 1992. Since then, it has become the de facto standard for application processing data in tabular format.
  • Examples of ODBC-enabled software include Microsoft Excel, Tableau, Crystal Reports, Microsoft Power BI, or any similar application (e.g., spreadsheet, word processor, data access and retrieval tool, etc.).
  • For ODBC to work, you must have access to an ODBC-enabled application, an ODBC driver manager, an ODBC driver, and a data source.