Open Database Connectivity (ODBC)

If you look under the hood of Access, you'll find that it uses a database language called SQL (Structured Query Language) to read, insert, update, and delete data. SQL grew out of a relational database research project conducted by IBM in the 1970s. It has been adopted as the official standard for relational databases by organizations such as the American National Standards Institute (ANSI) and the International Organization for Standardization (ISO). When you're viewing a query window in Design view, you can see the SQL statements that Access uses by first clicking the Design tab below Query Tools, clicking the arrow below the View button in the Results group, and then clicking the SQL View command.

In an ideal world, any product that "speaks" SQL should be able to "talk" to any other product that understands SQL. You should be able to build an application that can work with the data in several relational database management systems (RDMSs) using the same database language. Although standards exist for SQL, most software companies have implemented variations on or extensions to the language to handle specific features of their products. Also, several products evolved before standards were well established, so the companies producing those products invented their own SQL syntaxes, which differ from the official standard. An SQL statement intended to be executed by Microsoft SQL Server might require modification before it can be executed by other databases that support SQL, such as DB2 or Oracle, and vice versa.

To solve this problem, a group of influential hardware and software companies-more than 30 of them, including Microsoft Corporation-formed the SQL Access Group. The group's goal was to define a common base SQL implementation that its members' products could all use to "talk" to one another. The companies jointly developed the Common Language Interface (CLI) for all the major variants of SQL, and they committed themselves to building CLI support into their products. About a dozen of these companies jointly demonstrated this capability in early 1992.

In the meantime, Microsoft formalized the CLI for workstations and announced that Microsoft products-especially those designed for the Microsoft Windows operating system-would use this interface to access SQL databases. Microsoft calls this formalized interface the Open Database Connectivity (ODBC) standard. In the spring of 1992, Microsoft announced that more than a dozen database and application software vendors had committed to providing ODBC support in their products by the end of 1992. With Access, Microsoft provides the basic ODBC Driver Manager and the driver to translate ODBC SQL to the SQL understood by SQL Server. Microsoft has also worked with several database vendors to develop drivers for other databases.

Access was one of Microsoft's first ODBC-compliant products, and the ODBC Driver Manager is a standard part of Microsoft's operating system. Microsoft has further refined this architecture with ActiveX Data Objects (ADO). ADO is a special library of objects that you can use to fetch and modify information about the database structure and fetch and update data from any database, including Access. You can also fetch data from ODBC databases using the standard Data Access Objects (DAO) library used to manipulate native Access tables. After you've added the drivers for the other SQL databases that you want to work with, you can use Access to build an application using data from any of these databases.

Note: You can use ADO as a "universal interface" to both databases that support ODBC as well as to those that do not.