Performing Batch Updates : Recordset Update « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Recordset Update

Performing Batch Updates

 
Sub BatchUpdates()
    Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset

    Dim strSQL As String
    Dim lngUpdated As Long

    rst.ActiveConnection = CurrentProject.Connection
    rst.CursorType = adOpenKeyset
    rst.CursorLocation = adUseClient
    rst.LockType = adLockBatchOptimistic
    rst.Open ("Select * from Products ")

    strSQL = "UnitPrice < 30000"
    lngUpdated = 0

    rst.Find strSQL

    Do Until rst.EOF
        lngUpdated = lngUpdated + 1
        rst("UnitPrice") = rst("UnitPrice") * 1.1
        rst.Find strSQL, 1, adSearchForward
    Loop

    rst.UpdateBatch

    Debug.Print lngUpdated & " Records Updated"

    rst.Close
    Set rst = Nothing

End Sub

 

Related examples in the same category