Querying with the search string "database query" in MSDN brings back an inordinate number of references to technologies like DAO, RDO, ODBC, DB-LIB, and others. Except in rare, specific circumstances, you probably should not use any of these legacy data access technologies for new development. This is not to say they don't work; in many cases, they work fine, even in COM+. The reason you shouldn't base new development on them is because Microsoft has changed its data access strategy to become non-RDBMS centric. Where all the aforementioned technologies assume that you are accessing a relational database (Oracle, SQL Server, and Informix, for example), Microsoft's new data access technology, ADO, does not.
In the new nomenclature, ActiveX Data Objects (ADO) is an OLE-DB consumer. The term OLE-DB describes Microsoft's new, generic data access strategy. The term consumer is clear enough; it consumes data—that is, a data access client. I cannot, however, imagine a worse name than OLE-DB. OLE is a term falling out of use in favor of COM, and DB stands in direct contrast to what OLE-DB is all about—generic access to any kind of data source whether it is a relational database or not. Bad naming aside, OLE-DB is about providing access to any type of data—hierarchical, relational, flat, it doesn't matter. As long as someone writes an OLE-DB provider for the data source, any client can connect with it and retrieve information via ADO.
An OLE-DB provider is nothing more than a COM class that implements the minimum set of interfaces required by the OLE-DB specification. The actual manipulation of data, processing of a query string, and massaging return data into a tabular format is the responsibility of the OLE-DB provider author. OLE-DB is not a technology; it is an interface specification that, if followed, allows your data source to be accessed by OLE-DB based Microsoft technologies. Many database vendors now supply OLE-DB providers for their databases (relational and non-relational) in addition to (or even instead of) ODBC drivers.
There is often some confusion between Open Database Connectivity (ODBC) and OLE-DB, which is understandable. There is some overlap between the two (Microsoft provides an OLE-DB provider for ODBC, but we'll talk about that in a bit), and they were both created to solve similar problems. ODBC is a standardized relational database API created by Microsoft. As the primary desktop OS, Microsoft was able to coerce every relational database manufacturer to adhere to the ODBC specification and ship an ODBC driver (a DLL exporting this API) with their product. By providing a standard interface (granted, a Microsoft standard), it became possible to write generic query tools and client applications that could be written to the ODBC specification instead of some proprietary API from the database manufacturer. Thus, an ODBC client application could talk to any relational database. As an additional benefit, one database can always be swapped for another without requiring any changes to client code.
There are two problems with ODBC, however. The first is that ODBC is a C API and so is not COM accessible. The second problem is that ODBC is designed solely as an interface to relational databases—its APIs are designed specifically to ferry SQL to an RDBMS and retrieve tabular rowsets. Many of the ODBC method names even contain the word SQL. Although this is certainly no problem if you are only interested in accessing relational databases, it is awkward if you want to access non-relational databases that don't understand SQL. Microsoft wanted to remove this inherent limitation, and as part of something it calledUniversal Data Access (UDA) it wanted a more generic, interface-based mechanism of accessing any type of data from any type of data source. It should be possible to query an Excel spreadsheet in the same way you can query a database table. It should further be possible to join an Excel spreadsheet with a relational database table (Microsoft SQL Server 7 can do this). Thus, OLE-DB became the preferred back-interface.
ODBC has not vanished, however. There still exists a large number of ODBC drivers that provide access to many back-systems, the manufacturers of which might not have written OLE-DB providers yet. Because Microsoft has, for the most part, adopted ADO/OLE-DB approaches in lieu of ODBC-based ones (case in point, the new OLE-DB grid, combo-box, and so on included with VB 6), it would be nice if ADO could have access to ODBC data sources for which there is not an OLE-DB provider. Microsoft provides such a wrapper in the form of a generic OLE-DB provider for ODBC. This means that you can, through this component, use ADO to access any data source for which there is an ODBC driver.
As we discuss in Chapter 8, "Transactions," the ODBC driver manager is a COM+Resource Dispenser. This means that when a transactional COM+ object requests an ODBC connection via any data access technology (RDO, ODBC, ADO), the ODBC driver manager enlists the connection with the Distributed Transaction Coordinator (DTC). This is how COM+ transactions operate. If ADO requests an ODBC connection through the OLE-DB provider for ODBC, the connection is enlisted by the ODBC driver manager. If ADO requests a connection directly from an OLE-DB provider (one supplied by the database manufacturer), the provider itself can enlist the connection with the DTC because the ODBC driver manager is not involved.
Now that you know a little about why ADO should be used, here are a couple ADO examples to start you off. A detailed overview of ADO is a book in itself, but to soften the impact of learning COM+ and ADO at the same time, the examples shown in Listings A.1 through A.3 might be of use.
Example A.1. ADO Code to Retrieve a Recordset
Dim Query As String Dim Connection As String Dim DataSet As New ADODB.Recordset Query = "SELECT traderName, traderID FROM Traders" Connection = "DSN=TraderDemo;UID=gbrill;PWD=password;" DataSet.Open Query, Connection 'Code to iterate through recordset and manipulate individual rows DataSet.Close Set DataSet = Nothing
Example A.2. ADO Calling a Stored Procedure, No Resultset, But with Output Parameter
Dim Connect As New ADODB.Connection Connect.ConnectionString = "DSN=TraderDemo;UID=gbrill;PWD=password;" Connection.Open Set Command = New ADODB.Command Set Command.ActiveConnection = Connection Command.CommandText = "sp_GetTraderInfo" Command.CommandType = adCmdStoredProc Command.Parameters.Refresh Command.Execute() Debug.print "Paramater 1 = " & Command.Parameters(0).Value Debug.print "Parameter2 = " & Command.Parameters(1).Value
Example A.3. ADO Calling a Stored Procedure with Resultset and Output Parameters
Dim Connect As New ADODB.Connection Dim DataSet As New ADODB.Recordset Connect.ConnectionString = "DSN=TraderDemo;UID=gbrill;PWD=password;" Connection.Open Set Command = New ADODB.Command Set Command.ActiveConnection = Connection Command.CommandText = "sp_GetTraderInfo" Command.CommandType = adCmdStoredProc Command.Parameters.Refresh Set DataSet = Command.Execute() ' Code to iterate through recordset and manipulate individual rows DataSet.Close Debug.print "Paramater 1 = " & Command.Parameters(0).Value Debug.print "Parameter2 = " & Command.Parameters(1).Value