Sets the length of time to wait for a response to a command from the data source before issuing a timeout error message.

Holds the information needed to make the connection. This may include the name of the data provider, the name of the data source, a password, and a user ID. We will discuss connection strings at some length later in the chapter.

Sets the length of time to wait for a connection to be made before issuing a timeout error message.

Sets a recordset’s cursor (which is a device used to traverse the recordset and which defines the current recordset) to reside on the client side of the connection or on the server side. Typically, client-side cursors offer more capabilities than server-side, but server-side cursors may be better at reflecting changes to the data source made by other users. Ultimately, the choice of which type of cursor to use depends on the capabilities of the data provider and on the particular needs at the time. We will see examples of using both types of cursors later on.

By setting a default database for a particular connection, avoids the need to qualify each table name in an SQL statement with the database name.

Returns the Errors collection of all Error objects (if any) for the previous command.

Specifies the access mode for the connection and can be set to any one of the following:

Specifies the data provider. Note that the data provider can alternatively be specified in the ConnectionString property.

Returns the state of the connection (read-only). The possible values are given by the following enum:

Enum ObjectStateEnum
     adStateClosed = 0
     adStateOpen = 1
     adStateConnecting = 2
     adStateExecuting = 4
     adStateFetching = 8
End Enum

Returns the ADO version number as a string.

Closes the connection. Its syntax is simply:

cn.Close

Executes a command. A command can be a database query, an SQL statement, a stored procedure, or a provider-specific command in text form. We emphasize that the form of command depends on the data provider. For instance, not all data providers support stored procedures or even SQL statements.

Note that some commands return a recordset and some do not. Accordingly, there are two syntaxes for the Execute method:

' Syntax for a non recordset-returning command

ConnectionObject.ExecuteCommandText, RecordsAffected, Options

' Syntax for a recordset-returning command
Dim rs As ADODB.Recordset
Set rs = ConnectionObject.Execute(CommandText, RecordsAffected, Options)

We will see several examples of the use of the Execute method. RecordsAffected is a Long parameter that we must supply. ADO will fill this variable with the number of records that are affected by the command. The optional Options parameter can assume a variety of values indicating how the data provider should interpret the CommandText argument. The possible values are:

Opens a connection; that is, it creates an actual connection to the data provider. Its syntax is:

connection.OpenConnectionString, UserID, Password, Options

where all parameters are optional. The ConnectionString parameter is the tricky one here. We will discuss connection strings at length later in the chapter. Note that the Connection object has a ConnectionString property that can be used to set the connection string as well. However, the ConnectionString parameter will override any setting of the ConnectionString property.

Microsoft warns that we should not pass UserID and password values in both the ConnectionString property and the ConnectionString parameter of the Open method, for this may lead to unpredictable results. (And here I thought that computers did not produce unpredictable results.)

Note that it is important to close a connection using the Close method when the connection is no longer required. However, closing the connection does not remove the Connection object from memory, so its properties may still be accessed or altered. In order to remove the Connection object from memory, we must set the variable that references the Connection object to Nothing.

The Options parameter can assume one of the following values:

This type of cursor is automatically updated to show additions, deletions, and edits to the recordset made by other users. It also permits all forms of movement through the recordset that do not use bookmarks, as well as those that do use bookmarks if the provider supports bookmarks. (Note, however, that the provider must support bookmarks or backward cursor movement in order to use the MovePrevious method.)

This type of cursor is similar to a dynamic cursor, except that it does not show records that have been added by other users, nor does it allow access to records that have been deleted by other users. However, edits by other users are visible. Keyset cursors must support bookmarks and therefore allow all forms of movement through the recordset.

This type of cursor provides a static copy of a set of records. This is like a snapshot DAO recordset. Static cursors are used to find data or to generate reports. They must support bookmarks and therefore allow all forms of recordset movement. However, additions, deletions, and edits by other users are not visible. Note that all client-side cursors are static cursors. Even if we specify a different type of cursor for a client-side cursor, ADO will open a static cursor instead.

This type of cursor behaves identically to a dynamic cursor except that it permits only forward scrolling. This is the analog of supplying the dbForwardOnly constant as an argument to the DAO OpenRecordset method. As with forward-only DAO recordsets, forward-only cursors perform more efficiently when we need to make only a single pass through the recordset.

Records are read-only. Note that this is the default value, which means that if we want to do any editing, we must set this property to another value.

In this case, the data provider ensures successful editing of records, usually by locking records at the data source as soon as the Edit method is called. This is termed pessimistic locking. It occurs on a record-by-record basis.

In this case, the provider locks records only when the Update method is called. This is termed optimistic locking. It occurs on a record-by-record basis.

Optimistic batch updates are required for batch update mode.

To help the user page through the data in a recordset (especially when that data is intended to be displayed on the Web), ADO allows us to group the data into logical pages . (The page count starts at 1, by the way.) The PageSize property is used to specify the number of records per page (the default is 10 records per page).

The PageCount property returns the number of pages in the recordset. If a data provider does not support pages, it will indicate this by always returning a PageCount value of -1.

The AbsolutePage property is used either to set the current record at the beginning of a page or to return the page number of the current record. The return value of AbsolutePage may be a page number or one of the following values:

This property works like the corresponding DAO property; namely, it provides the ordinal position of the current record in the recordset (the first position is position 1). As with DAO, however, the AbsolutePosition property can change when another record is deleted or if the recordset is refreshed. Thus, we cannot rely on the value of AbsolutePosition to return to a given record at a later time. To mark a record for later retrieval, we should use bookmarks.

The ActiveConnection property of a recordset returns the connection string for the corresponding connection. If there is no active connection, it returns Nothing. For instance, in the code:

Dim rs As New ADODB.Recordset

Debug.Print rs.ActiveConnection
Debug.Print rs.ActiveConnection Is Nothing

the second line will produce a runtime error, whereas the third line will return True.

Thus, if the recordset rs is associated with the connection cn, then the following values are the same:

cn.ConnectionString
rs.ActiveConnection

For an open recordset, this property is read-only (as you would expect). However, for a closed recordset, we can set the ActiveConnection property to a valid connection string, and ADO will open the connection for us automatically. Setting the property to Nothing will disconnect the recordset from any provider.

Note that the ActiveConnection property can be set either to a string that specifies the connection or to a valid Connection object variable name.

We will have much more to say about connection strings later in the chapter. For now, we refer the reader to the RecordsetExample subroutine in Example 17-1 for an example of the ActiveConnection property. As mentioned earlier, querying the ActiveConnection property is one of the best ways to get the full syntax of a connection string for a data provider. Needing to resort to this technique is a reflection on the poor quality of the documentation for OLE DB data providers, especially when it comes to connect strings.

As with DAO, these Boolean properties indicate whether the current record pointer lies before the first record (BOF is True) or after the last record (EOF is True). In either case, there is no current record.

Each record in an ADO recordset has a bookmark associated with it. (A bookmark has Variant data type.) We can retrieve this bookmark and store it in a variable with code such as:

bk = rs.Bookmark

We can then return to this record at any time by writing:

rs.Bookmark = bk

This specifies the number of records that will be placed in the client-side memory buffer at one time. Put another way, it is the number of records that are fetched from the data store at one time.

As discussed earlier, this property specifies the location of the cursor: client-side or server-side.

As discussed earlier, this property specifies the type of cursor: dynamic, keyset, static, or forward-only.

Like DAO, ADO uses a temporary editing buffer for the current record. The EditMode property indicates the current status of the data in this buffer. Its possible values are:

This returns the Fields collection for the given recordset. We will discuss Field objects later in the chapter.

Filters the current recordset by restricting the records that are visible. Thus, for instance, after executing the code:

rs.Filter = "Lastname = 'Smith' OR FirstName Like 'A*'"

the recordset referenced by rs is filtered so that we have access only to those records that meet the filter condition. We can release the filter by writing:

rs.Filter = ""

Note that after setting a filter, the current record pointer is moved to the first record that fits the filter criteria. Note also that Microsoft warns that it is preferable to define and open a new recordset on the data source than to make extensive use of filters.

This limits the number of records returned by a query. The default value of 0 indicates that all matching records should be returned. This property is read-only for an open recordset.

This indicates the number of records in an open recordset. The property returns -1 when ADO cannot determine the number.

Note that if the recordset supports either approximate positioning or bookmarks (as indicated, for example, by the Supports method discussed later), then the RecordCount value is always correct regardless of whether the recordset has been fully populated by using the MoveLast method. Thus, if neither positioning nor bookmarks are supported, the only way to make sure that the RecordCount property is accurate is to populate the recordset fully, which may place a significant drain on resources because all records in the recordset will need to be retrieved from the data source.

This Variant property gives the source of the data for the recordset. It is read-only when the recordset is open. It can be set to a valid Command object variable name, an SQL statement, a table name, or a stored procedure call. (As always with ADO, this depends on the level of support from the data provider.) See the RecordsetExample subroutine in Example 17-1 for examples of the Source property.

This read-only property returns the state of the recordset. The possible values are given by the following enum :

Enum ObjectStateEnum
     adStateClosed = 0
     adStateOpen = 1
     adStateConnecting = 2
     adStateExecuting = 4
     adStateFetching = 8
End Enum

Adds new records to a recordset, provided that the data provider and the current cursor type support this feature, of course. The general syntax is:

recordset.AddNewFields, Values

where Fields is an optional single field name or an array of field names and the optional Values is the corresponding value (for a single field) or value array (for a field array) to assign to the fields in the new record. For instance, the code:

rs.AddNew Array(LastName, FirstName), Array("Einstein", "Albert")

adds a new record with values LastName = "Einstein" and FirstName = "Albert".

Creates a new Recordset object that is a duplicate of the Recordset object to which it is applied. It is important to note, however, that a cloned Recordset object is not entirely independent of its parent. Here is what the documentation says about cloned recordsets:

Changes made to one Recordset object are visible in all of its clones regardless of cursor type. However, after you execute Requery on the original Recordset, the clones will no longer be synchronized to the original.

Closing the original Recordset does not close its copies; closing a copy does not close the original or any of the other copies.

You can only clone a Recordset object that supports bookmarks. Bookmark values are interchangeable; that is, a bookmark reference from one Recordset object refers to the same record in any of its clones.

Closes the recordset.

Deletes one or more records. Its syntax is:

rs.Delete AffectRecords

where AffectRecords is one of the following constants:

Retrieves multiple records into an array. The syntax is:

array = recordset.GetRows(Rows, Start, Fields)

Rows is an optional Long parameter that specifies the number of rows to retrieve. Its default is adGetRowsRest, indicating that the method should retrieve all of the remaining records in the recordset. The optional Start parameter specifies the starting row to retrieve. It should be either a bookmark or one of the values: adBookmarkCurrent (start at the current record; this is the default), adBookmarkFirst (start at the first record), or adBookmarkLast (start at the last record). Finally, Fields can be a single field name (or ordinal position) or an array of field names (or ordinal positions). If the Fields parameter is not missing, only those fields will be returned; otherwise, all fields will be returned.

Note that the DAO version of the GetRows method has a different syntax.

Here are some things to keep in mind concerning the GetRows method:

Are used to move the current record pointer.

The Move method has the syntax:

recordset.MoveNumRecords, Start

where NumRecords is a Long specifying the number of records to move the current record pointer relative to Start, which is either a bookmark or one of the values adBookmarkCurrent, adBookmarkFirst, or adBookmarkLast.

According to the documentation, “the Move method is supported on all Recordset objects.” Of course, exactly what this means is unclear. Does it refer to all types of recordsets for a provider that supports the Move method, or does it mean that all providers must support this method?

If you are experiencing performance problems with Move, you might want to consider whether the CacheSize setting is causing too many retrievals. It may be possible to improve performance by setting the CacheSize value to a larger number. This is a tradeoff between performance and memory usage (as always).

Note that if the Recordset object to which we apply the Move method is forward-only, we can still pass a NumRecords value that is less than zero, provided that the destination is within the current set of cached records. If not, an error will occur. On the other hand, a call to MovePrevious will generate an error even if the resulting move lies within the currently cached group of records.

The MoveFirst, MoveLast, MoveNext, and MovePrevious methods work similarly to those methods in DAO. Note, however, that the Recordset object must support bookmarks or backward cursor movement in order to use the MovePrevious method. Otherwise, the method will generate an error. On the other hand, the MoveFirst method will work on a forward-only recordset, but it may cause the provider to re-execute the command that retrieved the Recordset object in the first place.

Makes it possible to set up a compound command that contains several individual commands. For instance, the statement:

SELECT * FROM table1;SELECT * FROM table2

consists of two separate SQL statements. If we execute this command using the Execute method, ADO will execute and retrieve only the first SQL statement. To execute the second command and get the corresponding recordset, we use the NextRecordset method. For more on this, we refer the reader to the ADO documentation.

Opens a recordset. The full syntax is:

recordset.OpenSource, ActiveConnection, CursorType, LockType, Options

As with the Connection object, the parameters are optional and can be specified separately using properties of the Recordset object.

The Source parameter specifies the data source. Setting this parameter will override the setting of the Source property (if any). The parameter can be set to a Variant that identifies a valid Command object variable name, or to an SQL statement, a table name, or a stored procedure call (if supported by the data provider, as usual).

Setting the ActiveConnection parameter will override the current value of the ActiveConnection property (if any). The setting can be the name of a valid Connection object variable or a string that describes the connection. This will cause ADO to establish (open) the connection.

For a discussion of the CursorType and LockType parameters, see Section 17.4.3.1 and Section 17.4.3.2 in the discussion of Section 17.4.3 earlier in this chapter. Note that if we set either of these parameters, the setting will also be made automatically in the corresponding property value.

The Options parameter is used when Source is a string (not a Command object) to identify the type of the Source argument. It can be one of the following values:

These values can be combined with values that relate to asynchronous fetching of records:

It is important to close a recordset using the Close method when the recordset is no longer required. However, closing the recordset does not remove the Recordset object from memory, so its properties may still be accessed or altered. In order to remove the Recordset object from memory, we must set the recordset variable that references the object to Nothing.

Updates the recordset by requerying the data source.

Resynchronizes the recordset with the underlying data. It differs from the Requery method in that it does not re-execute the original query that produced the recordset. Hence, it will cause any changes to existing records to be visible, but it will not show any new records.

Gets information on what features are supported for recordsets of the specified type by the data provider. The syntax is:

boolean = recordset.Supports(CursorOptions)

The return value is True if the feature described by CursorOptions is supported and False otherwise.

Here is a list of the possible values for CursorOptions:

To illustrate, the SupportsExample procedure in Example 17-2 compares static and dynamic cursors for a Jet connection.

Sub SupportsExample(  )

' Compares support options for static and dynamic cursors

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim lRecordsAffected As Long

' Set up connection
Set cn = New ADODB.Connection
cn.Provider = "Microsoft Jet 3.51 OLE DB Provider"
cn.ConnectionString = "Data Source=D:BkAccessIIAccessCode.mdb"
cn.Open

Set rs = New ADODB.Recordset

' ---------------------------------------------------
' Check support options for server-side static cursor
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM Names", cn, adOpenStatic, adLockOptimistic
' Get recordset support
Debug.Print
Debug.Print "Server-Side Static Recordset:"
Debug.Print "adAddNew: " & rs.Supports(adAddNew)
Debug.Print "adBookmark: " & rs.Supports(adBookmark)
Debug.Print "adDelete: " & rs.Supports(adDelete)
Debug.Print "adFind: " & rs.Supports(adFind)
Debug.Print "adUpdate: " & rs.Supports(adUpdate)
Debug.Print "adMovePrevious: " & rs.Supports(adMovePrevious)

rs.Close

' ----------------------------------------------------
' Check support options for server-side dynamic cursor
rs.CursorLocation = adUseServer
rs.Open "SELECT * FROM Names", cn, adOpenDynamic, adLockOptimistic
' Get recordset support
Debug.Print
Debug.Print "Server-Side Dynamic Recordset:"
Debug.Print "adAddNew: " & rs.Supports(adAddNew)
Debug.Print "adBookmark: " & rs.Supports(adBookmark)
Debug.Print "adDelete: " & rs.Supports(adDelete)
Debug.Print "adFind: " & rs.Supports(adFind)
Debug.Print "adUpdate: " & rs.Supports(adUpdate)
Debug.Print "adMovePrevious: " & rs.Supports(adMovePrevious)

rs.Close

cn.Close

End Sub

The output is:

Server-Side Static Recordset:
adAddNew: True
adBookmark: True
adDelete: True
adFind: True
adUpdate: True
adMovePrevious: True

Server-Side Dynamic Recordset:
adAddNew: True
adBookmark: False
adDelete: True
adFind: True
adUpdate: True
adMovePrevious: True

Thus, we can see that static cursors support bookmarks, whereas dynamic cursors do not.

Updates the current record after editing. This method can be used to set values as well, since its general syntax is:

recordset.UpdateFields, Values

where Fields is a single field name or an array of field names, and Values are the corresponding values to assign to the fields in the record. For instance, the code:

rs.Update Array(LastName, FirstName), Array("Einstein", "Albert")

updates the record by setting LastName = "Einstein" and FirstName = " Albert".

Sets the connection over which the command will be sent. As discussed earlier, it can be a text string (a connection string) or a Connection object variable.

Sets (or retrieves) the actual command. This is usually an SQL statement, but it can be any string that is recognized as a command by the data provider (such as a stored procedure call). According to the documentation, some data providers may alter the text of a command string. We can view any changes by examining the value of the CommandText property.

Sets or returns the length of time to wait for the command to execute before displaying a timeout error. The default is 30 seconds.

Sets the type of command; it has the same values as the Options parameter in the Open method of the Recordset object:

Can be used to assign a name to a command.

Returns a Parameters collection, which contains the parameters that are required by the command (if any). We will not discuss parameterized queries for ADO in this book.

If set to True, the data provider will compile the command specified in the CommandText property, assuming that it supports this feature. This may slow execution the first time that the command is executed. However, subsequent executions of the same command should proceed more quickly. Note that if the data provider does not support command compilation, it may return an error as soon as this property is set to True, or it may simply ignore the request to prepare the command and set the Prepared property to False.

Creates a Parameter object. A Parameter object represents a parameter that is associated with a parameterized query. We will not discuss parameterized queries for ADO in this book.

Executes the command represented by the Command object. As with the Execute method of the Connection object, there are two possible syntaxes based on whether or not the command returns a recordset:

' Syntax for a non recordset-returning command

CommandObject.ExecuteRecordsAffected, Parameters, Options

' Syntax for a recordset-returning command
Dim rs As ADODB.Recordset
Set rs =
CommandObject.Execute(RecordsAffected, Parameters,
Options )

Note that all parameters are optional.

The RecordsAffected parameter is a Long that returns the number of records affected by the command. The Parameters parameter is a Variant array of parameters that may be required by the SQL statement (if any). The values in this array will override any parameter values set through the Parameters property. (The order of parameters in the array is the order in which the parameters are passed.)

Finally, the Options parameter is equivalent to the CommandType property (and has the same possible values).

The DefinedSize property is used to set the size of a field as it is defined. The ActualSize property returns the size of the actual data stored in that field for the current record. Thus, for example, a String field named FirstName may have DefinedSize 25, but if the actual data in a given record at a particular time is "Albert", then the ActualSize property will return 6.

The Attributes property of a Field object can be a sum of the following values. Note that for a Field object, the Attributes property is read-only.

This is the name of the field. Note that the Name property is read-only for Field objects.

The read-only NumericScale property is used to return the number of digits to the right of the decimal place that is used to represent numeric values. The read-only Precision property returns the total number of digits used to represent a numeric value. Both are Byte properties.

The Value property sets or returns the value of the field for the current record.

The UnderlyingValue property returns the current field value from the database. This value may be the result of a recent update to the recordset by another transaction, whereas the OriginalValue property returns the original value that was retrieved from the recordset and thus does not reflect any updates by another transaction.

The UnderlyingValue and OriginalValue properties are read-only. To set a value, we must use the Value property.

This specifies the data type for the field. The possible values are listed earlier in Table 17-1.