Resources

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:

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:

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


See Also:

Implementing a Locking Strategy
Supported ADO Cursor and Lock Type Combinations