Microsoft Office 2000/Visual Basic Programmer's Guide

Recordset Object Basics

To work with records in a database by using Visual Basic or VBScript code, you use ADO Recordset objects. A Recordset object represents the records from a single table or the set of records returned by executing a command, such as an SQL string, an Access query, or a SQL Server stored procedure.

You can open a Recordset object in ADO by using any of these three methods:

The Execute method of the Connection object
The Execute method of the Command object
The Open method of the Recordset object

The syntax for each of these methods is as follows:

Set recordset = connection.Execute ([CommandText, [RecordsAffected, [Options]]])

Set recordset = command.Execute ([RecordsAffected, [Parameters, [Options]]])

recordset.Open [Source, [ActiveConnection, [CursorType, [LockType, [Options]]]]]

Although using the Execute method of the Connection or Command object returns a Recordset object, these methods are primarily intended for executing commands (typically SQL strings) that don't return records; queries that are called action queries in Access. When they are used to return a set of records, they create only Recordset objects of the Forward-only, Read-only cursor type, and there is no way to specify any other cursor type. Because of this limitation, we will not discuss using the Execute method of the Connection or Command object for opening Recordset objects for the purpose of creating a client-side set of records. For information about cursor types, see "Specifying Cursor Types" later in this chapter.

However, a Command object can be passed to the Open method of the Recordset object as the Source argument. This can be useful in two ways:

You can use the Prepared property of the Command object to optimize and precompile the command. If you will be using the Command object more than once within the scope of your procedure, this will optimize the performance of your procedure.
A Command object is required if you want to supply parameters to a query that can be reused efficiently.

These applications of the Command object are discussed later in this chapter. The following sections discuss how to use the Open method of the Recordset object, which provides more options and greater flexibility when you open Recordset objects.

Using the Open Method of a Recordset Object

The syntax for the Open method of an ADO Recordset object is as follows:

recordset.Open Source, ActiveConnection, CursorType, LockType, Options

As is true for other Visual Basic methods, you can pass each of the arguments of the Open method as either a positional argument or a named argument. Additionally, each of the arguments of the Open method (except for the Options argument) can also be specified by setting the corresponding properties of the Recordset object before invoking the Open method. For example, the following code fragment specifies the database connection and cursor type by setting the ActiveConnection and CursorType properties before opening the Recordset object, and then passes the name of the data source to the Open method as a named argument.

cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
With rst
   .ActiveConnection = cnn
   .CursorType = adOpenKeyset
   .Open Source:= "Employees"
End With

Neither method is better than the other; you can use either method or both together.

The following table briefly describes each of the arguments of the Open method.

Argument	Description
*Source*	Optional. A valid Command object variable name, an SQL statement, a table name, a query name (Access), a stored procedure name (SQL Server), or the file name of a Recordset object previously saved by using the Save method of the Recordset object.
*ActiveConnection*	Optional. A valid Connection object variable name, a String containing connection string parameters, or if you are creating a Recordset object associated with the current Access database, you can pass CurrentProject.Connection for this argument.
*CursorType*	Optional. A constant that determines the type of cursor that the provider should use when it opens the Recordset object.
*LockType*	Optional. A constant that determines what type of locking (concurrency) the provider should use when it opens the Recordset object.
*Options*	Optional. A constant that indicates how the provider should evaluate the *Source* argument if it represents something other than a Command object, or that the Recordset object should be restored from a file where it was previously saved.

Details on how to use each of these arguments are provided in the following sections.

Specifying the Records to Work With

When you open a Recordset object, you use the Source argument of the Open method to specify the set of records you want to open. The Source argument can be a valid Command object variable name that executes a command that returns records, an SQL statement, or a table name. If you are connecting to an Access database file, you can also pass the name of a query that returns records, or if you are connecting to a SQL Server database, you can pass the name of a stored procedure that returns rows.

If you pass anything other than a Command object, you must also use the ActiveConnection argument of the Open method to specify how to connect to the database.

Before you pass a Command object as the Source argument, its ActiveConnection property or argument must already be set to a valid Connection object or connection string. If you pass a Command object in the Source argument and also pass an ActiveConnection argument, an error occurs.

The following code fragment passes a string variable that contains the name of a table as the Source argument.

rst.Open    Source:=strSourceTable

Connecting a Recordset Object Variable

When you use some OLE DB providers (such as the Microsoft Jet 4.0 and SQL Server OLE DB providers), you can create Recordset objects independently of a previously defined Connection object by using the ActiveConnection argument of the Open method to pass a connection string. ADO still creates a Connection object, but it doesn't assign that object to an object variable. For example, the following code fragment opens a Recordset object by passing a connection string to the Open method.

Dim rstFieldData As ADODB.Recordset
Dim strConnect As String
Dim strSQL   As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   & "Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"
strSQL = "SELECT * FROM Customers WHERE Region = 'WA'"

Set rstFieldData = New ADODB.Recordset
' Specify the cursor type and lock type, and then
' open the Recordset object by passing criteria and the
' connection string to the Open method.
With rstFieldData
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open Source := strSQL, _
      ActiveConnection := strConnect, _
      Options:= adCmdText
End With

Important Note that the WHERE clause in the SQL statement in the previous example uses single-quote marks (') around the value specified in the criterion (Region = 'WA'). When you use DAO, a WHERE clause can have double-quote marks (") around a value, but in ADO you must use single-quote marks when specifying criteria. Additionally, if your criteria contain field names that have the same name as SQL keywords, you must surround the field name with square brackets ([ ]). For example, if the field name for the criterion in the previous example had been named Where, the criterion in the WHERE clause would have to look like this:

WHERE [Where] = 'WA'

However, if you plan on opening more than one Recordset object over the same connection, you will get better performance and use fewer resources if you explicitly create and open a Connection object and then pass this object variable to the Open method. This ensures that the Connection object is created only once. If you open your Recordset objects by passing a connection string, ADO creates a new Connection object for each new Recordset object, even if you pass the same connection string. The previous sample rewritten to pass a Connection object looks like this:

Dim cnnConnect      As ADODB.Connection
Dim rstFieldData    As ADODB.Recordset
Dim strConnect      As String
Dim strSQL          As String

strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
   & "Data Source=C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb;"
Set cnnConnect = New ADODB.Connection
cnnConnect.Open strConnect

strSQL = "SELECT * FROM Customers WHERE Region = 'WA'"

Set rstFieldData = New ADODB.Recordset
With rstFieldData
   .CursorType = adOpenForwardOnly
   .LockType = adLockReadOnly
   .Open Source := strSQL, _
      ActiveConnection := cnnConnect, _
      Options := adCmdText
End With

Specifying Cursor Types

At any given time, a Recordset object can only refer to one record within the set as the current record. The software functionality that lets you work programmatically with a set of records is referred to as a cursor. You can think of a cursor as a device that you can use to scroll through a set of records in a database to read, add, delete, or update records. There are four types of cursors for Recordset objects in ADO: Dynamic, Keyset, Static, and Forward-only.

The functionality available to each of the cursor types depends on how the cursor manages currency and membership.

Currency is a measure of how current the data is that is displayed for records available to the cursor. The data displayed by Dynamic and Keyset cursor types are regularly updated to reflect changes that have been made by other users or processes. The data displayed in Static and Forward-only cursor types represents a snapshot of the data made at the time the Recordset object was opened; however, it is possible to force an update of one or more records in these cursor types by using the Resync method.

Membership describes whether the set of records available to a cursor automatically reflects any newly added or deleted records. New additions or deletions are available to the Dynamic cursor type, so its membership is not fixed. New additions or deletions are not reflected in the Keyset, Static, and Forward-only cursor types, so their membership is fixed. You can manually refresh all of the data in these cursor types by using the Requery method, which is equivalent to closing and then reopening the Recordset object.

To specify the cursor type, set the CursorType property of a Recordset object before you open it. For example, the following code fragment creates a Recordset object and sets its cursor type to open as a Keyset cursor.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.CursorType = adOpenKeyset

You can also specify a cursor type by passing the CursorType argument to the Open method of a Recordset object, as shown in the following code fragment.

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.Open _
   Source:=strSourceTable, _
   ActiveConnection:=cnn, _
   CursorType:=adOpenKeyset

If you don't specify a cursor type, ADO opens a Forward-only cursor by default.

Note Some providers don't support all cursor types, so check the documentation for the provider. For information about the cursor types supported by the Microsoft Jet 4.0 OLE DB Provider, see "ADO Equivalents to DAO Recordset Types" later in this chapter.

The following table describes the features of each cursor type and lists the constants you can use to set or read the CursorType property.

Cursor type	Constant	Description
Dynamic	adOpenDynamic	Reflects any new additions, changes, and deletions made by other users, and allows all types of movement through the Recordset object that don't rely on bookmarks; allows bookmarks if the provider supports them. This cursor type isn't supported by the Microsoft Jet 4.0 OLE DB Provider.
Keyset	adOpenKeyset	Behaves like a Dynamic cursor, except that it doesn't contain any new or deleted records added by other users. Any data changes made by other users to the records available when the Recordset object was opened will still be visible. A Keyset cursor always supports bookmarks and therefore allows all types of movement through the Recordset object.
Static	adOpenStatic	Provides a static, but updatable, copy of a set of records. Always allows bookmarks and therefore allows all types of movement through the Recordset object. Any additions, changes, or deletions by other users will not be visible until the Resync method is called. This is the only type of cursor allowed when you open a client-side (ADOR) Recordset object.
Forward-only	adOpenForwardOnly	Behaves identically to a Static cursor except that it only allows you to scroll forward through records. This improves performance in situations where you need to make only a single pass through a Recordset object. (Default) Note The RecordCount property for a forward-only Recordset object always returns – 1 because ADO can't determine the number of records in a forward-only Recordset object. To get a valid count of records when using a Recordset object to work with an Access database, you must use either a Keyset cursor or a Static cursor.

Specifying Locking

The LockType property or argument specifies what kind of locking is used while you edit records in the Recordset object. Locking is used to regulate what other users in a multiuser database can do with a record while it is being edited. If you don't set the LockType property, read-only locks will be used by default, which will prevent you from editing records. To edit the data in a Recordset object, you must set the LockType property before you open it, or else you must pass the LockType argument to the Open method. The LockType property is read/write before a Recordset object is opened (or after it is closed), and read-only while it is open. The following table lists the constants you can use to set or read the LockType property and describes how each lock functions when you are editing records.

Constant	Description
adLockReadOnly	Read-only — you cannot edit the data. (Default)
adLockPessimistic	Pessimistic locking, record by record — the provider does what is necessary to ensure successful editing of the records, usually by locking records at the data source as soon as you start editing records. No other users can read or edit the data until you either save changes with the Update method or cancel them with the CancelUpdate method.
adLockOptimistic	Optimistic locking, record by record — the provider locks records only when you call the Update method. Other users can read, edit, and save changes to the same record while you have it open.
adLockBatchOptimistic	Optimistic batch updates — required for batch update mode as opposed to immediate update mode.

The following addition to the previous code example specifies optimistic locking, so that the Recordset object can be edited.

rst.Open _
   Source:=strSourceTable, _
   ActiveConnection:=cnn, _
   CursorType:=adOpenKeyset, _
   LockType:=adLockOptimistic

Some OLE DB providers may not support all lock types. If a provider cannot support the requested LockType setting, it will substitute another type of locking. To determine the actual locking functionality available in a Recordset object, you can use the Supports method with adUpdate and adUpdateBatch constants. For more information about the Supports method, see "Determining Cursor Features" later in this chapter.

For a more detailed discussion of locking and other multiuser database issues, see Chapter 16, "Multiuser Database Solutions."

Optimizing the Evaluation of the Source Argument

If you pass something other than a Command object in the Source argument to the Open method of the Recordset object, you can use the Options argument to optimize evaluation of the Source argument. If you don't specify the Options argument, you may experience diminished performance, because ADO must make calls to the provider to determine if the Source argument is an SQL statement, a stored procedure, or a table name. If you know what type of object or command you're using for the Source argument, setting the Options argument instructs ADO to jump directly to the relevant code. You can use one of the following constants to specify the Options argument.

Constant	Description
adCmdText	Indicates that the provider should evaluate the *Source* argument as a textual definition of a command.
adCmdTable	Indicates that ADO should generate an SQL query to return all records from the table named in the Source argument.
adCmdTableDirect	Indicates that the provider should return all records from the table named in the Source argument.
adCmdStoredProc	Indicates that the provider should evaluate the *Source* argument as a stored procedure.
adCmdUnknown	Indicates that the type of command in the *Source* argument is not known.
adCmdFile	Indicates that a persisted (saved) Recordset object should be restored from the file named in the *Source* argument.
adAsyncExecute	Indicates that the *Source* argument should be executed asynchronously.
adAsyncFetch	Indicates that after the initial quantity specified in the CacheSize property is fetched, any remaining records should be fetched asynchronously.

The following addition to the previous code sample ensures that the Source argument is evaluated as a table name.

rst.Open _
   Source:=strSourceTable, _
   ActiveConnection:=cnn, _
   CursorType:=adOpenDynamic, _
   LockType:=adLockOptimistic, _
   Options:=adCmdTableDirect

The default for the Options argument is adCmdFile if no connection is associated with the Recordset object. This will typically be the case for saved Recordset objects.

Specifying the Cursor Location

ADO also supports two kinds of cursor engines: the server cursor engine and the client cursor engine. The location of the cursor engine determines certain aspects of the functionality of ADO Recordset objects.

To specify which cursor engine to use, you use the CursorLocation property of the Recordset or Connection object. Set the CursorLocation property to adUseServer to use the server cursor engine, and to adUseClient to use the client cursor engine. To specify the cursor engine for a Recordset object, you must set the CursorLocation property before opening the Recordset object. If you set the CursorLocation property of a Connection object, any Recordset object you open while using that Connection object will inherit that connection's setting. By default, when opening a Connection object for an Access database, the CursorLocation property is adUseServer.

To determine which cursor engine to use — client or server — you should know the following:

The server cursor engine is provided by the OLE DB provider and the database engine itself, which for Access databases means the Microsoft Jet 4.0 OLE DB Provider and the Microsoft Jet database engine. For SQL Server databases it means the Microsoft SQL Server OLE DB Provider and either SQL Server or the Microsoft Data Engine (MSDE). In both cases, the database engine builds a set of keys, called a keyset, which is stored locally for Access databases and on the server for SQL Server databases. The database engine then uses the keyset to retrieve and scroll through the records in the database.
The client cursor engine is an OLE DB service component that buffers and copies the specified records to a temporary table that is stored locally in memory, or on disk, if the number of records is sufficiently large. If you make changes to field data, or call the AddNew or Delete method on a client-side cursor, ADO automatically generates the appropriate SQL UPDATE, INSERT, and DELETE statements and sends them to the database engine when you update the Recordset object.

A cursor generated by the server cursor engine is often called a server-side cursor, and a cursor generated by the client cursor engine is called a client-side cursor. The differences between them are as follows:

The features provided by server-side cursors are specific to the database engine and OLE DB provider you are using. For Access databases, a Recordset object that uses a server-side cursor is generally more likely to be updatable than a client-side cursor, and when the database is shared by multiple users, a server-side cursor can show changes made by other users.
You can think of a client-side cursor as an updatable snapshot. By specifying the adBatchOptimistic constant for the LockType argument of the Open method of a Recordset object that is using a client-side cursor, you can use the UpdateBatch method to send updates to the database as a batch. Because the client cursor engine is an OLE DB service provider, the features provided by client-side cursors are fairly consistent across all data sources and OLE DB providers.

As a general rule, you should use server-side cursors for working with Access databases, and client-side cursors when working with SQL Server databases. However, although using client-side cursors with Access databases generates additional overhead to cache records and doesn't expose all of the functionality of the Jet database engine, you still may want to use client-side cursors with Access databases if you are working with a remote database, or if you need to ensure uniformity of behavior when working with multiple data sources.

ADO Equivalents to DAO Recordset Types

The combined CursorType, LockType, and Options arguments of the ADO Open method determine the type of ADO Recordset object that is returned. The table below shows how the Type and Options arguments of the DAO OpenRecordset method can be mapped to ADO Recordset Open method argument settings when you use ADO and the Microsoft Jet 4.0 OLE DB Provider to work with Access databases.

DAO arguments		ADO arguments
Type	Options	CursorType	LockType	Options
dbOpenDynaset		adOpenKeyset	adLockOptimistic
dbOpenSnapshot		adOpenStatic	adLockReadOnly
dbOpenSnapshot	dbForwardOnly	adOpenForwardOnly	adLockReadOnly
dbOpenTable		adOpenKeyset	adLockOptimistic	adCmdTableDirect

Not all of the possible combinations of ADO Recordset CursorType and LockType arguments are listed above, because the Microsoft Jet 4.0 OLE DB Provider doesn't support all combinations of the CursorType and LockType arguments. Additionally, the setting of the CursorLocation property for the Recordset object, which specifies whether to use the server cursor engine or the client cursor engine, also determines the kind of functionality available to the Recordset object. If your ADO code uses a combination of CursorLocation property and CursorType and LockType argument settings that aren't supported by the Microsoft Jet 4.0 OLE DB Provider, it will then return a Recordset object that it does support.

The table below describes the results you can expect for all combinations of the CursorType and LockType arguments when using the Microsoft Jet 4.0 OLE DB Provider with server-side cursors (CursorLocation = adUseServer).

CursorType	LockType	Results
adOpenForwardOnly	adLockReadOnly	You can only scroll forward one record at a time. If you try to scroll backwards, ADO will requery the Recordset object and start from the beginning. You can't update data.
adOpenForwardOnly	adLockOptimistic adLockPessimistic adBatchOptimistic	For all other lock types, a Keyset cursor is returned.
adOpenStatic	adLockReadOnly	The Recordset object contains a scrollable, read-only snapshot of your data. You can't see changes made by other users.
adOpenStatic	adLockOptimistic adLockPessimistic adBatchOptimistic	For all other lock types, a Keyset cursor is returned.
adOpenKeyset	adLockReadOnly	The Recordset object has a read-only, scrollable cursor. You can see updates and deletions, but not insertions made by other users. If the Recordset object is opened by specifying a table name for the *Source* argument and the adCmdTableDirect constant for the *Options* argument, you will be able to use the Index property to use table indexes for scrolling and searching for records.
adOpenKeyset	adLockOptimistic	The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is not locked to save updates to the current record until the Update method is called, or when you move to a different record. If the Recordset object is opened by specifying a table name for the *Source* argument and the adCmdTableDirect constant for the *Options* argument, you will be able to use the Index property to use table indexes for scrolling and searching for records.
adOpenKeyset	adBatchOptimistic	The Recordset object has an updatable, scrollable cursor. When using server-side cursors, the batch size is limited to one record. This makes this type of Recordset object functionally similar to the previous entry, except that updates to the current record aren't automatically saved if you move to a different record without calling the UpdateBatch method. The updated record is not locked until the UpdateBatch method is called. If the Recordset object is opened by specifying a table name for the *Source* argument and the adCmdTableDirect constant for the *Options* argument, you will be able to use the Index property to use table indexes for scrolling and searching for records.
adOpenKeyset	adLockPessimistic	The Recordset object has an updatable, scrollable cursor. You can see updates and deletions, but not insertions made by other users. The record is locked when the first field is modified. If the Recordset object is opened by specifying a table name for the *Source* argument and the adCmdTableDirect constant for the *Options* argument, you will be able to use the Index property to use table indexes for scrolling and searching for records.
adOpenDynamic	adLockReadOnly	Dynamic cursors are not supported for Access databases by the server cursor engine. A Static cursor is returned instead.
adOpenDynamic	adLockOptimistic adLockPessimistic adBatchOptimistic	Dynamic cursors are not supported for Access databases by the server cursor engine. If you request a Dynamic cursor for all other lock types, you will receive a Keyset cursor. However, ADO uses different methods to manipulate the Recordset object if you open it by using the *adOpenDynamic* argument for the *CursorType* argument. This may result in improved performance when scrolling large numbers of records compared to an explicitly requested Keyset cursor.
Not Specified	adLockReadOnly	If you don't specify the *CursorType* argument, a Static cursor is returned.
Not Specified	adLockOptimistic adLockPessimistic adBatchOptimistic	If you don't specify the *CursorType* argument for all other lock types, a Keyset cursor is returned.

If you use the Microsoft Jet 4.0 OLE DB Provider with client-side cursors (CursorLocation = adUseClient), the provider doesn't support Forward-only (adOpenForwardOnly), Keyset (adOpenKeyset), or Dynamic (adOpenDynamic) cursors. If you attempt to set the CursorType argument to any of these settings, the provider will return a static (adOpenStatic) cursor. For client-side cursors, the only CursorType setting supported is a static (adOpenStatic) cursor. The following table describes the results you can expect when using each of the LockType settings with a static cursor from the client cursor engine.

LockType	Results
adLockReadOnly	The Recordset object contains a scrollable snapshot of your data. You will not see updates, insertions, or deletions made by other users unless you use the Requery method.
adLockOptimistic	The Recordset object contains a scrollable, updatable snapshot of your data. Changes are made on a record-by-record basis.
adBatchOptimistic	The Recordset object contains a scrollable, updatable snapshot of your data. Changes can be grouped into batches and processed all at once by using the UpdateBatch method.
adLockPessimistic	Pessimistic locks are not supported by the client cursor engine. An Optimistic lock is used instead.

Determining Cursor Features

You can use the ADO Supports method to determine what types of functionality are supported when you open a Recordset object against the current OLE DB provider. The Supports method returns a Boolean value that indicates whether the provider supports the features identified by the various CursorOptions constants. For example, the adAddNew constant is used to determine if the current Recordset object supports your using the AddNew method for adding new records.

Note Although the Supports method may return True for a given functionality, it does not guarantee that the OLE DB provider can make the feature available under all circumstances. The Supports method simply tells whether or not the provider can support the specified functionality, assuming that certain conditions are met. For example, the Supports method may indicate that a Recordset object supports updates even though the cursor is based on a multi-table join, some fields of which are not updatable.

For more information about the Supports method, see ADO Help. For an example of a procedure that lists the features supported by any OLE DB provider, see the ProviderSupports procedure in the OpenRecordset module in the DataAccess.mdb sample file, which is available in the ODETools\V9\Samples\OPG\Samples\CH14 subfolder on the Office 2000 Developer CD-ROM.