Updating Records

In this recipe, you learn how to perform recordset updates using the IOM and SAS/SHARE providers. Information about batch updating is included.

Applies to:	SAS IOM Data Provider, SAS/SHARE Data Provider
Implement using:	ADO

Overview

To perform recordset updates, set the following recordset properties:

Set the CommandType property to adCmdTableDirect.
Note: If 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 rowset is opened.
Set the LockType property to either adLockBatchOptimistic, adLockOptimistic, or adLockPessimistic. For information about selecting a lock type, see Implementing a Locking Strategy.
If possible, set the CursorType property to a value other than its default, which is adForwardOnly.

For information specific to batch updating, see the Details section.

Details

Recordset objects that are opened 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 will ignore the batch request and update 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.

Managing the Row Cache When Updating Batches

The SAS Data Providers maintain a row cache. When you open a Recordset object 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 OpenRows" property.

Here is an example of how to update records using the adLockBatchOptimistic lock type.

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset

cn.Provider = "sas.ShareProvider.1"
cn.Properties("Data Source") = "shr1"
cn.Properties("Location") = "lambchop.unx.sas.com"
cn.Properties("User ID") = "fred"
cn.Properties("Password") = "banana"
cn.Open

rs.ActiveConnection = cn
rs.CursorType = adOpenStatic
rs.LockType = adLockBatchOptimistic

' Set the maximum number of rows for batch update
rs.Properties("Maximum OpenRows") = 1000

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

' The ActiveConnection, CursorType, and LockType have been set above
' Therefore, these values are not passed to the Open call

rs.Open "mydata.finance", , , , adCmdTableDirect

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

Resources

Updating Records

Overview

Details

Managing the Row Cache When Updating Batches

See Also: