Using Batch Updates

The Update method of the Recordset object allows you to update the current row. The UpdateBatch method applies all pending new, updated, and deleted rows to the Recordset object. Using a LockType property value of adLockBatchOptimistic, the UpdateBatch method allows you to commit all pending changes at the client and send all the changes to the database at one time. The pending changes can be canceled by calling the CancelBatch method.

With the UpdateBatch method, an error is returned if all the changes fail to be applied to the database. If only some of the changes fail, a warning is returned instead of an error, by using the Errors collection and Error object.

The UpdateBatch method is valid only when the LockType property is specified with adLockBatchOptimistic and the cursor type is either keyset-driven or static. The keyset-driven cursor can be supported only with tables that have unique indexes.

This example shows the use of the UpdateBatch method to apply all pending changes; it creates a recordset by using the keyset-driven cursor with the LockType property set to adLockBatchOptimistic. After the Recordset object is created, the user is prompted to change any row in the titles table of pubs with a type of psychology to self help. Clicking OK commits the changes using the UpdateBatch method; clicking No cancels the changes using the CancelBatch method. The routine at the end restores the original values to the table.:

Public Sub UpdateBatchX()

   Dim rstTitles As ADODB.Recordset
   Dim strCnn As String
   Dim strTitle As String
   Dim strMessage As String

   ' Assign connection string to variable.
      strCnn = "Provider=sqloledb;" & _
      "Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "

   Set rstTitles = New ADODB.Recordset
   rstTitles.CursorType = adOpenKeyset
   rstTitles.LockType = adLockBatchOptimistic
   rstTitles.Open "titles", strCnn, , , adCmdTable
   
   rstTitles.MoveFirst

   ' Loop through recordset, and prompt user for 
   ' change of type for a specified title.
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "psychology" Then
         strTitle = rstTitles!Title
         strMessage = "Title: " & strTitle & vbCr & _
            "Change type to self help?"

         If MsgBox(strMessage, vbYesNo) = vbYes Then
            rstTitles!Type = "self_help"
         End If
      End If

      rstTitles.MoveNext
   Loop

   ' Ask if the user wants to commit to all the 
   ' changes made earlier.
   If MsgBox("Save all changes?", vbYesNo) = vbYes Then
      rstTitles.UpdateBatch
   Else
      rstTitles.CancelBatch
   End If

   ' Print current data in recordset.
   rstTitles.Requery
   rstTitles.MoveFirst
   Do While Not rstTitles.EOF
      Debug.Print rstTitles!Title & " - " & rstTitles!Type
      rstTitles.MoveNext
   Loop

   ' Restore original values because this is a demonstration.
   rstTitles.MoveFirst
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "self_help" Then
         rstTitles!Type = "psychology"
      End If
      rstTitles.MoveNext
   Loop
   rstTitles.UpdateBatch

   rstTitles.Close

End Sub