The Update method of the Recordset object allows you to update the current record. The UpdateBatch method applies all pending new, updated, and deleted records 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.
In Microsoft® SQL Server™, the UpdateBatch method is only valid when the LockType property is specified with adLockBatchOptimistic and the cursor type is either keyset-driven or static. The keyset-driven cursor can only be supported with tables that have unique indexes.
This example shows the use of the UpdateBatch method to apply all pending changes:
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
This example created 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.