ODBCDirect
Microsoft developed ODBCDirect for use in Office applications that can't use RDO because of licensing complications. Think of ODBCDirect as Microsoft's way of providing Office developers the advantages of RDO without requiring them to purchase VB, Enterprise Edition. Because ODBCDirect uses RDO, it's a solution for 32-bit systems only.
VB Library for SQL Server
In the beginning, only C programmers had programmatic access to SQL Server. SQL Server's native interface was DB-Library. Microsoft developed VB Library for SQL Server (VBSQL) to provide VB programmers access to SQL Server. VBSQL comprises a VBX (updated to an Object Linking and Embedding custom control—OCX—for VB 4.0 and later) and API declarations. The custom control provides some hooks that developers couldn't create using VB alone. A few versions ago, Microsoft added ODBC as a native interface to SQL Server.
VBSQL, like ODBC, provides an API paradigm. One feature previously unique to VBSQL was asynchronous error and message handling.
Microsoft has enhanced VBSQL with each release of SQL Server except 7.0. So, although you can still use VBSQL to access SQL Server 7.0, you cannot take advantage of any features unique to version 7.0.
At one time, you could argue that VBSQL provided functionality and performance sufficiently superior to other SQL Server access methods to justify VBSQL's relative difficulty in use. But that day is long past. Although programmers no longer use VBSQL for speed, it is a well-documented, well-tested access method. It's been around long enough that users have worked out most of its kinks.
OLE DB
If all these data-access technologies haven't made you dizzy yet, then you won't appreciate OLE DB, the result of Microsoft's vision of a uniform database access method. Probably not an end-all solution (developers and users will always dream up new features), OLE DB is a big step in the right direction.
Leveraging the heterogeneous data-access capabilities of DAO and the standards-based interface ODBC provides, OLE DB is a specification to which you can map any data. This functionality gives you a standard interface for accessing that data.
OLE DB is a low-level interface designed for use by driver vendors and proprietary database vendors who want to expose a data source to ADO-aware applications or by C++ developers who want to develop custom data components. You cannot call OLE DB directly from VB because it isn't COM automation-compatible.
As OLE DB becomes more prevalent, you'll hear the following terms more often (paraphrased from Microsoft literature):
- Data providers are components that represent data sources such as SQL databases, ISAM databases, spreadsheets, and other forms of storing data. Providers use a common abstraction, the rowset, to expose information consistently.
- Services are components that consume and produce OLE DB data. For example, a cursor engine is a service component that can consume data from a sequential, forward-only data source to produce scrollable data. A relational query engine is an example of a service over OLE DB data that produces rowsets satisfying a Boolean predicate.
- Consumers are components that consume OLE DB data. Examples of consumers include services such as a query processor; high-level data access models such as ADO; business applications written in languages such as VB, C++, or Java; and development tools.
ActiveX Data Object
ActiveX Data Object (ADO) is an object model on OLE DB, and Microsoft intended ADO to end the confusion over which data-access model to select. ADO will do it all.
ADO is to OLE DB as RDO is to ODBC. Whereas RDO is an abstraction that connects to any ODBC provider, ADO is an abstraction that connects to any OLE DB provider. Because ADO is COM-based, you can use it from any COM-compatible application, including VB. VB 6.0 includes ADO version 2.0 and the documentation on how to use it.
How important is ADO to VB developers? Consider that Microsoft has made VB designers, wizards, and even the debuggers ADO-aware. Is ADO a panacea? Not exactly. Besides requiring that you learn another programming model, ADO has the disadvantage of a memory footprint rivaled only by DAO. But Microsoft has explicitly stated that ADO is the future of data access for VB programmers.
Other Considerations
When selecting a database-access technology, first consider whether the target desktop is 16-bit or 32-bit. Because VB 5.0 and later exclusively target 32-bit systems, developers targeting 16-bit systems are constrained to VB 4.0 or VB 3.0, which limits their database-access options.
Also consider scalability. If you intend to use Microsoft Transaction Server (MTS), the data-access interface you choose must be executed on the server and must be capable of acting as a resource manager. This requirement precludes the use of DAO, but you can use the ODBC API, RDO, or ADO.
In addition, consider how you will distribute the application. Dealing with the interdependencies of redistributables is beyond the scope of this article, but support concerns might arise, so keep abreast of these factors and consider them when you select a data- access technology.
Although choosing a data-access method isn't straightforward, the expanded options offer flexibility for VB programmers.