Mark Gendron
Microsoft Developer Network Technology Group
August 15, 1996
Microsoft offers a wide (and often confusing) array of technologies for accessing data sources from your application. This article attempts to put the various technologies in perspective, describing how they fit vis-à-vis one another, and what their capabilities are.
The technologies covered in this article include the following:
This is a working document and it will be expanded from time to time. New database technologies will be included in this document as more information about them becomes available.
The venerable DBLIB is implemented as an application programming interface (API), and is the "native" protocol for connecting an application to Microsoft® SQL Server. Because callback functions are required to handle messages and errors returned by Microsoft SQL Server, a custom control (VBSQL) is available to implement DBLIB in Visual Basic® applications. Both 16-bit and 32-bit versions of the VBSQL custom control are currently available.
Use of DBLIB has declined somewhat since the introduction of ODBC and other methods for accessing Microsoft SQL Server. DBLIB may still be the right choice where every last bit of performance must be squeezed from a system. This advantage has become less significant than it once was, as other data access methods (notably ODBC) have become more efficient.
Because it is implemented as an API, DBLIB is more difficult to develop with than newer data access methods such as Data Access Objects (DAO) or Remote Data Objects (RDO). Because DBLIB is compatible only with Microsoft SQL Server, code written using DBLIB cannot be easily leveraged to work with other database products.
Open Database Connectivity (ODBC) is a standard protocol that allows applications to use Structured Query Language (SQL) to communicate with any external data source for which an ODBC driver is available. ODBC is implemented as an API, and experienced DBLIB developers will find many of its functions quite familiar.
Although applications developers can program directly to the ODBC API, ODBC is also commonly used as a foundation upon which other data access tools are built. For example, both the Remote Data Controls and the Remote Data Objects are built on ODBC. ODBC may be used by Microsoft Jet for accessing external data sources.
ODBC defines three conformance levels: the Core API, Level 1 API, and Level 2 API. ODBC drivers should support the Core API functions, which do the following:
Ideally, an ODBC driver should also support the Level 1 API functions, which will make the driver compatible with most ODBC applications. In addition to the Core API functions listed above, the Level 1 API does the following:
The Level 2 API includes advanced features, including arrays of parameters and scrollable cursors.
The Microsoft Jet database engine is the data manager on which Microsoft Access is built. Jet databases may be manipulated via the following technologies:
The Jet database engine includes a query processor, which may be circumvented in favor of a query processor built into an ODBC driver or a database management system (DBMS). For example, queries to an .MDB file will use the Jet query processor to parse and execute the query. Queries submitted by the Visual Basic Data Objects may be designated as a "SQL Passthrough" query. This causes the Jet query processor to be bypassed, although the recordset processor is still used to manage any results returned by the query. Note that DAO 3.1 and later versions can submit queries to external databases via ODBC without invoking Jet at all.
Data Access Objects (DAO) is a set of objects that enable OLE Automation clients to access data in Jet databases or ODBC data sources. Data Access Objects are used by Microsoft Access, Visual Basic, and other Microsoft products.
Data Access Objects are supported by the Visual Basic standard edition and professional edition. The standard edition supports Data Access Objects via the Data control, while the professional edition also permits instantiation of Data Access Objects using the Dim statement.
DAO version 2.5 is a 16-bit implementation that is shipped with Visual Basic 4.0. The current 32-bit implementation, DAO 3.0, is also shipped with Visual Basic 4.0. DAO 2.5 does not support all of the features of version 3.0, such as replication support. If you need to recompile an older Visual Basic project for 32-bit, and this project uses DAO, you should specify the DAO 2.5/3.0 compatibility library.
The Visual Basic Data control takes advantage of Data Access Objects to provide even simpler data access via bound controls. The DBList, DBGrid and DBCombo controls, when bound to a Data control, can provide nearly all DAO functionality without the need to write code. In the Visual Basic standard edition, DAO objects cannot be instantiated using the Dim statement, so the Data control must be used if your application requires data access.
If you need to access a remote database (such as Microsoft SQL Server or Oracle) using ODBC, Remote Data Objects (RDO) are your best choice. RDO is available only in the Visual Basic enterprise edition.
RDO is a very thin object layer built on top of the ODBC API and Driver Manager; RDO also implements some ODBC features that are not available from DAO. Such features include access to the ODBC connection and environment handles, output parameters, and return codes. RDO also allows you to specify which type of cursor (if any) will be used with your results set. This allows you to specify only as much cursor complexity as you require, which can make your application more efficient. You may use client-side or server-side cursors, depending on what your DBMS and ODBC driver support.
Although RDO will work with any ODBC data source, it is optimized for use with remote databases. RDO is an appropriate choice when you are working with a remote database, or anytime you need to get closer to the ODBC API.
The RemoteData control was introduced in the Visual Basic 4.0 enterprise edition. It is similar to the Data control in that it uses Remote Data Objects to enable data-bound controls. The DBList, DBGrid, and DBCombo controls can be bound to the RemoteData Control as well as the Data control. As with DAO, the data-bound controls will provide nearly all RDO functionality without the need to write code.
Formerly known as SQL OLE, SQL DMO provides an object layer over the Microsoft SQL Server data manipulation language (DML) functions. SQL DMO provides any OLE automation controller with the ability to manipulate the Microsoft SQL Server database objects, such as tables, stored procedures, triggers, views, database properties, and so on.
A good overview of SQL DMO, titled "SQL-DMO: What It Is and How to Use It," can be found on the MSDN Library.
Microsoft's newest data access technology is OLE DB. OLE DB will complement ODBC by allowing all data sources, not just full-fledged databases, to expose their data through a common set of COM interfaces. Data from e-mail, files, spreadsheets, and documents, as well as databases, will be accessible through OLE DB.
OLE DB recognizes components as service providers and data providers. A data provider is any component that exposes functionality through an OLE DB interface over a native data format. A service provider is an OLE DB component built to add advanced features (such as query processors or report generators) to data providers.