Updating Recordsets

Goal

You want your application to perform recordset updates, including batch updates.

This recipe applies to the SAS/SHARE, IOM, and Base SAS providers. This recipe applies only to ADO. Sample code for batch updating is included.

Implementation

The Recordset Property Values

To perform recordset updates, you set the Recordset properties to the values shown in the following table.

ADO Recordset Properties and Values

ADO Recordset Property	Value
CommandType	adCmdTableDirect1
LockType	adLockBatchOptimistic, adLockOptimistic, or adLockPessimistic For information about using adLockBatchOptimistic, see Sample Code for Using adLockBatchOptimistic.
CursorType	If possible, set the property to a value other than its default, which is adForwardOnly.
1If you set the CommandType property to a value other than adCmdTableDirect, the LockType property might revert to adLockReadOnly. Because the provider is not guaranteed to honor the requested lock type, you should always check the value of LockType after the recordset is opened.

Sample Code for Using adLockBatchOptimistic

The SAS providers maintain a row cache. When you open a Recordset object by using the adLockBatchOptimistic lock type, each altered record is added to the cache. The maximum number of records allowed in the cache is set by the "Maximum Open Rows" property.

A call to the UpdateBatch method transmits each modified row to the data source and flushes out the row cache. For the call to be successful, you must limit the number of rows that are updated between each call to UpdateBatch. The number of rows that are updated between each call should not exceed the value of the "Maximum Open Rows" property.

The following Visual Basic code shows you how to update records by using the adLockBatchOptimistic lock type:

' obConnection is an open Connection object.
Dim obRecordset As New ADODB.Recordset

' Set the ActiveConnection, CursorType, and LockType here instead of on the Open method. 
obRecordset.ActiveConnection = obConnection
obRecordset.CursorType = adOpenStatic
obRecordset.LockType = adLockBatchOptimistic

' Set the maximum number of rows for batch update.
obRecordset.Properties("Maximum Open Rows") = 1000

' Set exclusive member-level lock. All other users get read-only access.
obRecordset.Properties("SAS Optimistic Locking") = False

obRecordset.Open "mydata.finance", , , , adCmdTableDirect

' Update every row, setting the BALANCE column of each row to 0.
While Not obRecordset.EOF
 obRecordset!BALANCE = 0 
 obRecordset.MoveNext
Wend
obRecordset.UpdateBatch

A Closer Look at adLockBatchOptimistic

Recordset objects that are opened by using the adLockBatchOptimistic lock type have the following limitations:

SAS permits batch updates only when the data set has been opened with an exclusive member lock. If a data set has not been opened with an exclusive member lock, then the UpdateBatch method ignores the batch request and updates the records one at a time. To enable an exclusive member lock, set the customized Recordset property "SAS Optimistic Locking" to False.
Note: An exclusive member lock means that only one user can update the data set. So, only one user at a time can perform batch updates.
The number of records that can be updated with one batch update is limited by the value of the "Maximum Open Rows" property. The default value of this property is 100. If you need to update more than 100 records at a time, then change the property value to a higher number.