DAO and RDO were written when all of the database clients are single threaded monolithic applications – hence they were not written from the ground up to be thread safe. Microsoft will continue to support DAO and RDO but no new functionality will be planned. The long term plan is to get the customers to migrate to ADO and to focus the efforts on making ADO the high level API of choice for all database clients.

The ADO Object Model Overview

The ADO object model is very simple (3 major objects and 4 minor objects) and fairly simple and intuitive compared to Microsoft’s previous database object models:

Connection - Maintains connection information with the data provider. This includes methods for making database transactions (BeginTrans, CommitTrans and RollbackTrans). Also, it includes an Execute method for executing SQL Data Manipulation Language (DML) and Data Definition Language (DDL) statements that do not require parameters. There is an OpenSchema method for querying a database system for schema (sometimes called Meta) information such as lists of system tables, indexes on tables, etc…

Command – It holds information about a command such as a query string, parameter definitions, etc. You can also execute a command string on a Connection object or a query string as part of opening a Recordset object without defining a Command object. The Command object is useful when you want to define query parameters, or execute a stored procedure that returns output parameters. Basically, the Command object is useful for preparing a SQL statement and providing parameters to the statement if needed.

Recordset - It is a container that holds the set of records returned from a database through a query or stored procedures. You can open a Recordset (i.e., execute a query) without explicitly opening a Connection object. However, if you do first create a Connection object, you can open multiple Recordset objects on the same connection. The ADO Recordset is very similar to DAO’s Recordset and RDO’s rdoResultset object. The ADO Recordset has the additional advantage of allowing complete disconnection from the back-end database system (this is called a “disconnected” Recordset) as well as built-in cross-process and cross-machine remotability.

Field – It contains information about a single column of data within a Recordset. The Recordset object features a Fields collection to contain all of its Field objects. With the Field object you can gather information about a column in a table, such as the data-type of the column, nullability, and the amount of data it can hold (maximum characters for example). The ADO Field object is similar to both DAO’s and RDO’s Field/rdoField objects.

Parameter - A single parameter for a parameterized Command. The Command object features a Parameters collection for holding all of its Parameter objects. Also, ADO’s Parameter’s collection has a handy “auto-populate” feature that makes it much easier to use with SQL Server stored procedures and parameterized SQL statements provided if the driver is sophisticated enough to provide parameter meta-information (SQL Server and Oracle Drivers are good in this respect -- the Microsoft Access ODBC Driver is not as good).

Error - Contains extended error information about an error condition raised by the provider. Since a single statement can generate two or more errors, the Errors collection can contain more than one Error object at a time, all of which result from the same incident. Similar to Error/rdoError in DAO/RDO.

Property - A provider-defined characteristic of an ADO object. Every ADO object contains a properties collection which can be iterated programmatically.

Connecting To Databases with ADO

To connect to a database with ADO you can use one of two general methods.

·          Connection.Open method

·          Using Command or Recordset Object

Connecting using Connection.Open

 The first method is to use the Connection.Open method. The resulting opened connection object can be used directly or passed to other ADO objects.

Note that ADO provides access to both OLE DB providers and ODBC drivers. ADO does this by using a special OLE DB provider that translates ADO’s OLE DB calls to correspondingly equivalent ODBC calls. This translator provider is code named “Kangera”and its provider name is “MSDASQL”. The MSDASQL provider is the default provider for ADO. In other words, if you do not explicitly specify a provider, then the MSDASQL provider is used automatically. This makes using ADO with ODBC drivers very straightforward. If the customer understands ODBC connection strings, then connecting to an ODBC driver using ADO will be very simple. Just pass the ODBC connection string as the first parameter to the Connection.Open method.

Sub ADO_ODBC_CONNECTION_TEST()

Dim conn As New ADODB.Connection

conn.Open "DSN=LocalServer;DATABASE=pubs;UID=sa;PWD=;"

If conn.State = adStateOpen Then

Debug.Print "Connection successfully opened."

Else

Debug.Print "Connection failed."

End If

End Sub

If the customer wants to use an OLE DB provider, then the specific provider name must be set to over-ride the MSDASQL default provider. This can be set individually (by using the Provider property of the Connection object) or by adding a PROVIDER= statement to the OLE DB connection string as below.

Sub ADO_OLEDB_CONNECTION_TEST()

Dim conn As New ADODB.Connection

conn.Provider = "SQLOLEDB"

conn.Open "SERVER=UKDUDE;DATABASE=Pubs;UID=sa;PWD=;"

If conn.State = adStateOpen Then

Debug.Print "Connection successfully opened."

Else

Debug.Print "Connection failed."

End If

End Sub

Once the Connection object is open, you can then pass the connection to an ADO Command or Recordset object by setting the next object’s ActiveConnection property to the connection object.

Dim conn As New ADODB.Connection

Dim rs As ADODB.Recordset

‘ Open connection ...

Set rs.ActiveConnection = conn

Please note the use of the “Set” keyword here which is required when assigning object type variables in VBA.

Using Command or Recordset Object

ADO also has a short-hand method of opening a database connection that bypasses the connection object altogether. Both the recordset and the command object will allow you to pass a connection string instead of a connection object to their respective ActiveConnection properties. Just set the Command/Recordset’s ActiveConnection property to the desired connection string and the object is ready to use.

Sub ADO_COMMAND_CONNECTION_TEST()

Dim cmd As New ADODB.Command

Dim rs As ADODB.recordset

Dim strConn As String

cmd.ActiveConnection = " DRIVER={SQL Server};" & _

"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"

cmd.CommandText = "byroyalty"

cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh

cmd.Parameters(1).Value = 25

Set rs = cmd.Execute

' Recordset now has authors with 25% royalty.....

End Sub

Note that here I did not use the “Set” keyword as in the previous example. I am assigning a string to a property which is not the same as assigning a VBA object.

Note that this second method is actually a more desired approach than explicitly using a connection object in code. In general, most ODBC drivers do not support more than one active statement per connection. This means that sharing a single Connection object over more than one Command/Recordset object can cause errors due to multiple active statements running. If the customer uses the connection string approach, then each Command/Recordset object will have it’s own individual connection object internally and “multiple active statements per connection” errors will be avoided.

Sample ODBC and OLE DB Connection Strings

Many customers are confused when using ODBC and OLE DB connection strings. One of the most helpful articles I found to explain various connection strings is the “Setting Connection String Parameters in DAO” whitepaper.

http://msdn.microsoft.com

Here are some simple examples to connect to various ODBC drivers and OLE DB Providers.

Microsoft Access

ODBC = “DRIVER={Microsoft Access Driver (*.mdb)};DBQ=C:\NW.MDB”

OLE DB = “PROVIDER=Microsoft.JET.OLEDB.3.51;DATA SOURCE= C:\NW.MDB”

Microsoft SQL Server

ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"

OLE DB = PROVIDER=SQLOLEDB;SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"

Microsoft Oracle

ODBC = “DRIVER={SQL Server};SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"

OLEDB = “PROVIDER=MSDAORA; SERVER=MyServer;DATABASE=pubs;UID=xxx;PWD=yyy;"

Microsoft Excel

ODBC= "Driver={Microsoft Excel Driver (*.xls)};DBQ=C:\Book1.xls"

Microsoft FoxPro

ODBC=”DRIVER={Microsoft FoxPro Driver (*.dbf)};DBQ=C:\FoxFiles;”

Using the ADO Command Object

General Command Object Issues

The ADO Command object is used when one needs to provide parameter information to a stored procedure, a SQL DML statement, or a SQL select statement. Parameters are indicated by embedding a single question mark in the location when the parameter is desired. Parameters can only replace literal values in a SQL statement, they cannot be used to indicate a variable field names for example.

Here are some example SQL statements that use parameters:

select * from authors where au_id=’243-11-2334’

select * from authors where au_id=?

insert into MyTable (field1,field2) values (1,’hello’)

insert into MyTable (field1,field2) values (?,?)

{call MyStoredProcedure(‘la’,’dee’,’dah’)}

{call MyStoredProcedure(?,?,?)

Parameter markers are simply replaced from left to right as encountered. The first parameter encountered is parameter 0 (parameter’s collection is zero based).

ADO provides an “auto-populate” feature for parameters which is quite handy. If the driver supports it, you can call ADO’s Parameters.Refresh method and this will automatically build the parameters collection for you (rather than manually adding each parameter one at a time programmatically).

Sub ADO_PARAM_TEST()

Dim conn As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADODB.recordset

cmd.ActiveConnection = "Driver={SQL Server};" & _

"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"

cmd.CommandText = "select * from authors where au_id=?"

cmd.CommandType = adCmdText

cmd.Parameters.Refresh ‘ Auto-populate here…

cmd.Parameters(0).Value = "213-46-8915"

Set rs = cmd.Execute

' Read record here...

End Sub

For more information on manually populating the parameters collection, see the online examples in the ADO documentation. Unfortunately, the Microsoft Access ODBC and OLE DB drivers do not currently support automatic parameter population. This has been submitted as a feature request for future versions of the driver.

Using Stored Procedures with Command Objects

When calling stored procedures using the Command object, set the Command’s CommandText to just the name of the stored procedure, then set the CommandType property to the adCmdStoredProc constant to let ADO now that the SQL statement in the CommandText property is a stored procedure.

Sub ADO_STORED_PROC_TEST()

Dim conn As New ADODB.Connection

Dim cmd As New ADODB.Command

Dim rs As New ADODB.recordset

cmd.ActiveConnection = "Driver={SQL Server};" & _

"Server=UKDUDE;DATABASE=pubs;UID=sa;PWD=;"

cmd.CommandText = "byroyalty"

cmd.CommandType = adCmdStoredProc

cmd.Parameters.Refresh

' Skip parameter 0 which is the return value!

cmd.Parameters(1).Value = 25

rs.Open cmd, , adOpenStatic, adLockOptimistic, -1

End Sub

You can determine which parameters are bound by running the code example in the following KB article against your particular stored procedure:

HOWTO: Determine How ADO Will Bind Parameters

http://support.microsoft.com/kb/q181199/

Using the ADO Recordset Object

General Recordset Issues

The ADO Recordset object is very similar to the Recordset objects in Microsoft’s previous database API’s. The Recordset has the concept of a “current record” or “record pointer” which points to the currently selected record. The programmer can move the current record pointer forwards and backwards by using the MoveNext and MovePrevious methods. In most cases the developer simply loops through a set of records until the Recordset EOF flag is set to True.

Dim rs As New ADODB.Recordset

rs.ActiveConnection = "Driver = {SQL Server};" & _

"Server=UKDUDE; DATABASE=pubs; UID=sa; PWD=;"

rs.Open “select * from authors”

While Not rs.EOF

‘ Process record here…

Rs.MoveNext

Wend

The developer can also use the Fields collection to dynamically gather more information about the various columns in the recordset.The Fields collection of the Recordset object allows the “For Each” VBA collection syntax which makes coding quite efficient.