لطفا منتظر باشید ...
Understanding ADO Recordset Types
Three parameters of the Open method of a Recordset object affect the type of recordset that is created. They are the CursorType, the LockType, and the Options parameters. These parameters combine to determine the types of movements that you can execute within a recordset, when changes that other users make to data underlying the recordset will be seen, and whether the recordset's data can be updated.
The CursorType Parameter
By default, when you open a recordset, the CursorType parameter is set to adOpenForwardOnly. This means that you can only move forward through the records in the recordset. You will not see any additions, edits, or deletions that other users make. Furthermore, many properties and methods, such as the RecordCount property and the MovePrevious method, are unavailable. Listing 14.6 illustrates this.
Listing 14.6 The RecordCount Property Is Not Supported with a Forward-Only Recordset
Sub ForwardOnlyRecordset()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish a connection and open a forward-only recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "Select * from tblClients"
'Attempt to retrieve the record count
Debug.Print rst.RecordCount
'Close and destroy the recordset
rst.Close
Set rst = Nothing
End Sub
The value -1 displays in the Immediate window because the forward-only recordset does not support the RecordCount property. Because you did not explicitly designate the cursor type, a forward-only recordset was created.Three other values are available for the CursorType. They are adOpenStatic, adOpenKeyset, and adOpenDynamic. The adOpenStatic option allows forward and backward movement through the records in the recordset, but changes that other users make to the underlying data are not seen by the recordset. The adOpenKeyset option offers everything offered by the adOpenStatic option, but in addition, edits that other users make are seen by the recordset. Finally, with the adOpenDynamic option, additions, edits, and deletions made by other users are seen by the recordset. Table 14.2 illustrates each of these options in further detail.
| Value | Description |
|---|---|
| adOpenForwardOnly | Copies a set of records as the recordset is created. Therefore, it doesn't show changes made by other users. This is the fastest type of cursor, but only allows forward movement through the recordset. |
| adOpenStatic | Copies a set of records as the recordset is created. Supports bookmarks and allows forward and backward movement through the recordset. Doesn't show changes made by other users. This is the only type of recordset allowed when using client-side cursors. |
| adOpenKeyset | Provides a set of pointers back to the original data. Supports bookmarks. Shows changes made by other users. Does not show new records, and provides no access to deleted rows. |
| AdOpenDynamic | Provides access to a set of records. Shows all changes, including additions and deletions, made by other users. |
Listing 14.7 Supplying the CursorType as a Parameter of the Open Method
Sub StaticRecordset1()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Establish a connection and open a static recordset
rst.ActiveConnection = CurrentProject.Connection
rst.Open "Select * from tblClients", _
CursorType:=adOpenStatic
'Retrieve the record count
Debug.Print rst.RecordCount
rst.Close
Set rst = Nothing
End Sub
Notice that, in Listing 14.7, the CursorType appears as a parameter of the Open method. Contrast Listing 14.7 with Listing 14.8.
Listing 14.8 Supplying the CursorType as a Property of the Recordset Object
Sub StaticRecordset2()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection and CursorType properties
'of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
'Open the recordset
rst.Open "Select * from tblClients"
'Retrieve the record count
Debug.Print rst.RecordCount
rst.Close
Set rst = Nothing
End Sub
In Listing 14.8, the CursorType is set as a property of the Recordset object, prior to the execution of the Open method. Separating the properties from the Open method improves the readability of the code.
The LockType Parameter
Although the CursorType property of a Recordset object determines how movements can occur within the recordset, and whether other users' changes are seen, the CursorType in no way affects the updateability of the recordset's data. In fact, when you open a recordset, you open it as read-only by default. It is only by changing the LockType property that you can make the recordset updateable.The options for lock type are adLockReadOnly, adLockPessimistic, adLockOptimistic, and adLockBatchOptimistic. The default, adLockReadOnly, does not allow changes to the recordset. The other options all provide updateability for the recordset's data. The difference is when the records are locked. With the adLockPessimistic option, locking occurs as soon as the editing process begins. With the adLockOptimistic option, the record is locked when you issue the Update method. Finally, with the adLockBatchOptimistic option, you can postpone locking until you update a batch of records. All these options are discussed in extensive detail in Alison Balter's Mastering Access 2002 Enterprise Development .As with the CursorType property, you can set the LockType property as a parameter of the Open method or as a property of the Recordset object. Listing 14.9 shows the configuration of the lock type as a property of the Recordset object.
Listing 14.9 Configuration of LockType as a Property of the Recordset Object
Sub OptimisticRecordset()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection, CursorType, and
'LockType properties of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
'Open the recordset
rst.Open "Select * from tblClients"
'Modify the contents of the city field
rst("City") = "Westlake Village"
rst.Update
Debug.Print rst("City")
rst.Close
Set rst = Nothing
End Sub
In Listing 14.9, the LockType property is set to adLockOptimistic. The record is locked when the Update method of the Recordset object is issued.NOTEListing 14.9 references the field name in the format rst("City"). You can use any one of four syntactical constructs to reference a member of a collection. These include the following:Collection("Name")
Collection(VariableName)
Collection!Name
Collection(Ordinal)
You might wonder which is best. Although all are valid, I most prefer the Collection("Name") and Collection(VariableName) methods. I like the fact that the syntax is the same whether you are supplying a string or a variable. Furthermore, the same syntax works with Active Server Pages (ASP). The bang does not work with ASP, and you cannot rely on the ordinal position because it changes. One of the only instances when you must use a bang is when you are supplying a parameter for a query. Besides that, I use the Collection("Name") syntax in the ADO and DAO code that I write.
The Options Parameter
The Options parameter determines how the provider should evaluate the source argument. The valid choices are illustrated in Table 14.3.
| Value | Description |
|---|---|
| adCmdText | The provider evaluates the source as a command. |
| adCmdTable | A SQL query is generated to return all rows from the table named in the source. |
| adCmdTableDirect | The provider returns all rows in the table named in the source. |
| adCmdStoredProc | The provider evaluates the source as a stored procedure. |
| adCmdUnknown | The type of command in the source is unknown. |
| adCmdFile | The source is evaluated as a persisted recordset. |
| adAsyncExecute | The source is executed asynchronously. |
| adAsyncFetch | The initial quantity specified in the Initial Fetch Size property is fetched. |
| adAsyncFetchNonBlocking | The main thread never blocks when fetching. |
Listing 14.10 The Options Parameter of the Open Method
Sub OptionsParameter()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection, CursorType, and
'LockType properties of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
'Open the recordset, designating that the source
'is a command
rst.Open "Select * from tblClients", _
Options:=adCmdText
'Modify the contents of the city field
rst("City") = "Westlake Village"
rst.Update
Debug.Print rst("City")
rst.Close
Set rst = Nothing
End Sub
In Listing 14.10, the Options parameter is set to adCmdText. This causes the source to be evaluated as a SQL command.
Consistent Versus Inconsistent Updates
When a recordset is based on data from more than one table, Jet automatically allows you to make changes to the foreign key field. For example, if a recordset is based on data from the Customers table and the Orders table, you are able to make changes to the CustomerID in the Orders table. This is referred to as a consistent update . At times you might want to make changes to the primary key field. This could result in a violation of referential integrity and is therefore referred to as an inconsistent update .If you've established referential integrity and have designated that you want to cascade updates, consistent and inconsistent updates yield the same results. On the other hand, without cascade updates activated, a change to the primary key field causes referential integrity to be violated.Listing 14.11 shows you how to open a recordset with inconsistent updates.
Listing 14.11 Opening a Recordset with Inconsistent Updates
Sub InconsistentUpdates()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection, CursorType, and
'LockType properties of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
'Open the recordset, designating that the source
'is a SQL statement based on more than one table
rst.Properties("Jet OLEDB:Inconsistent") = True
rst.Open Source:="Select * from tblClients " & _
"INNER JOIN tblProjects " & _
"ON tblClients.ClientID = tblProjects.ClientID", _
Options:=adCmdText
'Modify the contents of the foreign key field
rst("tblProjects.ClientID") = 1
rst.Update
Debug.Print rst("tblProjects.ClientID")
rst.Close
Set rst = Nothing
End Sub
Notice that the code sets the Jet OLEDB:Inconsistent property prior to the Open method of the recordset. This causes the recordset to be opened so that you can use inconsistent updates if you want.NOTEVery few providers support inconsistent updates. In fact, the Jet Provider is one of the few providers that supports this feature.
Selecting a Cursor Location
A cursor refers to the set of rows or row pointers that are returned when you open a recordset. With DAO, the location of the cursor is not an issue. On the other hand, ADO supports two cursor locations. As its name implies, the client manages a client-side cursor. The server manages a server-side cursor.If you are using Jet, the client machine always manages the cursor because Jet only runs on the client machine. You might think this means that you should always designate a client-side cursor when working with Jet. Actually, the opposite is true. If you designate a client-side cursor when working with Jet, the data is cached twice on the client machine. When a client-side cursor is specified, the Microsoft Cursor Service for OLEDB requests all the data from the OLEDB provider and then caches it and presents it to the application as a static recordset. For this reason, when working with Jet, you should only designate a client-side cursor when you want to take advantage of functionality provided only by a client-side cursor.Listing 14.12 illustrates how to designate the cursor location.
Listing 14.12 Designating the Cursor Location
Sub CursorLocation()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection, CursorType,
'LockType, and CursorLocation properties of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.CursorLocation = adUseServer
'Open the recordset, designating that the source
'is a SQL statement
rst.Open Source:="Select * from tblClients ", _
Options:=adCmdText
'Modify the contents of the city field
rst("City") = "New City"
rst.Update
Debug.Print rst("City")
rst.Close
Set rst = Nothing
End Sub
In the example, a server-side cursor is designated.
Working with the Supports Method
Depending on which CursorType, LockType, CursorLocation, and Provider are used to open a recordset, the functionality of the recordset varies. The Supports method of a recordset determines which features a particular recordset supports. It returns a Boolean value designating whether the selected feature is supported. Listing 14.13 provides an example.
Listing 14.13 The Supports Method of the Recordset Object
Sub SupportsMethod()
'Declare and instantiate a Recordset object
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
'Set the ActiveConnection, CursorType,
'LockType, and CursorLocation properties of the recordset
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.LockType = adLockOptimistic
rst.CursorLocation = adUseServer
'Open the recordset, designating that the source
'is a SQL statement
rst.Open Source:="Select * from tblClients ", _
Options:=adCmdText
'Determine whether the recordset supports certain features
Debug.Print "Bookmark " & rst.Supports(adBookmark)
Debug.Print "Update Batch " & rst.Supports(adUpdateBatch)
Debug.Print "Move Previous " & rst.Supports(adMovePrevious)
Debug.Print "Seek " & rst.Supports(adSeek)
rst.Close
Set rst = Nothing
End Sub
