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

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.


     < Day Day Up > 


    Automating Microsoft Access with VBA
    Automating Microsoft Access with VBA
    ISBN: 0789732440
    EAN: 2147483647
    Year: 2003
    Pages: 186

    Similar book on Amazon

    flylib.com © 2008-2017.
    If you may any questions please contact us: flylib@qtcs.net