Creating and Opening a Recordset

Create a Recordset by using the Dim keyword to declare it in the form

 Dim recordset As ADODB.Recordset Set recordset = New ADODB.Recordset 

Populating a Recordset is as simple as opening it. To do so, use the Open method in the form

 recordset.Open source[, activeconnection][, cursortype][, locktype][, options] 

where recordset represents a Recordset object. Table 16.4 lists the method's arguments.

Table 16.4. Recordset Object Arguments

Argument	Explanation
source	Identifies the data ADO uses to populate the Recordset object. This setting can be the name of a Command object, a SQL statement, the name of a table, a stored procedure, a persisted Recordset, a Stream object or a URL.
activeconnection	Specifies the connection to use to grab the data identified by source. Use a Connection object or a connection string.
cursortype	Specifies the type of cursor and is one of four intrinsic constants: adOpenDynamic, adOpenKeyset, adOpenStatic, adOpenForwardOnly, which are similar to the cursor types.
locktype	Specifies the record-locking behavior and is one of the intrinsic constants listed in Table 16.5.
options	Supplies additional information that the provider might need. Table 16.6 lists the intrinsic constants for this argument.

Table 16.5. locktype Argument Constants

Constant	Locking Behavior
adLockReadOnly	This default option creates a read-only Recordset.
adLockPessimistic	Records are locked while being edited.
adLockOptimistic	Records are locked only when updated.
adLockBatchOptimistic	Used with the UpdateBatch method to update multiple records with a single operation.

Table 16.6. options Argument Constants

Constant	Explanation
adCmdUnknown	The default option; supplies no additional information.
adCmdText	Identifies the CommandText property as a stored procedure (the actual commands, not the name of the procedure).
adCmdTable	Identifies the CommandText property as the name of a table.
adCmdStoredProc	Identifies the CommandText property as the name of a stored procedure.
adCmdFile	Identifies the CommandText property as the name of a file.
adCmdTableDirect	Identifies the CommandText property as the name of a table. The resulting Recordset is the only type that can support the Seek method.
adAsyncExecute	Executes the command asynchronously.
adAsyncFetch	Specifies that the cache should be filled synchronously and then additional rows fetched asynchronously.
adAsyncFetchNonBlocking	Retrieves records asynchronously if possible without blocking the main thread.

As you can see, there can be a lot to consider when opening a Recordset object. However, the good news is that most of the time the operation is really very simple. For instance, the following procedure populates a default Recordset object with the contents of the Clients table:

 Private Sub ClientsRst()   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   rst.Open "Clients", CurrentProject.Connection   MsgBox "Connection Made"   rst.Close   Set rst = Nothing End Sub 

If you decide to run it from a standard module, you'll find that it really doesn't do anything. It creates the Recordset object, but it doesn't do anything with the data that the Recordset object contains. However, this particular method is probably the most succinct for creating a Recordset object.

TIP

If you're just learning ADO, be careful about setting the Recordset type. By default, ADO opens a forward-only Recordset type, which is different from DAO. DAO defaults to a table, and then a dynaset, and then a snapshot, and finally a forward-only type. ADO retains the forward-only type only for compatibility.

One problem you might run into when trusting defaults is counting records. The forward-only Recordset type doesn't support the RecordCount property. If you need to use this property, be sure to set a static or keyset cursor.