Creating and Opening a Recordset

 < Day Day Up > 

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




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.


Specifies the connection to use to grab the data identified by source. Use a Connection object or a connection string.


Specifies the type of cursor and is one of four intrinsic constants: adOpenDynamic, adOpenKeyset, adOpenStatic, adOpenForwardOnly, which are similar to the cursor types.


Specifies the record-locking behavior and is one of the intrinsic constants listed in Table 16.5.


Supplies additional information that the provider might need. Table 16.6 lists the intrinsic constants for this argument.

Table 16.5. locktype Argument Constants


Locking Behavior


This default option creates a read-only Recordset.


Records are locked while being edited.


Records are locked only when updated.


Used with the UpdateBatch method to update multiple records with a single operation.

Table 16.6. options Argument Constants




The default option; supplies no additional information.


Identifies the CommandText property as a stored procedure (the actual commands, not the name of the procedure).


Identifies the CommandText property as the name of a table.


Identifies the CommandText property as the name of a stored procedure.


Identifies the CommandText property as the name of a file.


Identifies the CommandText property as the name of a table. The resulting Recordset is the only type that can support the Seek method.


Executes the command asynchronously.


Specifies that the cache should be filled synchronously and then additional rows fetched asynchronously.


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.


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.

     < Day Day Up > 

    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186 © 2008-2017.
    If you may any questions please contact us: