UpdateBatch and CancelBatch Methods Example
This example demonstrates the UpdateBatch method in conjunction with CancelBatch method.
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 = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstTitles = New ADODB.Recordset
rstTitles.CursorType = adOpenKeyset
rstTitles.LockType = adLockBatchOptimistic
rstTitles.Open "titles", strCnn
With rstTitles
.MoveFirst
' Loop through recordset and ask user if she wants to
' change the type for a specified title.
Do Until .EOF
If Trim(!Type) = "psychology" Then
strTitle = !Title
strMessage = "Title: " & strTitle & vbCr & _
"Change type to self help?"
If MsgBox(strMessage, vbYesNo) = vbYes Then
!Type = "self_help"
End If
End If
.MoveNext
Loop
' Ask if the user wants to commit to all the changes
' made above.
If MsgBox("Save all changes?", vbYesNo) = vbYes Then
.UpdateBatch
Else
.CancelBatch
End If
.Close
End With
rstTitles.Open "titles", strCnn
With rstTitles
' Print current data in recordset.
.MoveFirst
Do While Not .EOF
Debug.Print !Title & " - " & !Type
.MoveNext
Loop
' Restore original values because this is a demonstration.
.MoveFirst
Do Until .EOF
If Trim(!Type) = "self_help" Then
!Type = "psychology"
End If
.MoveNext
Loop
.UpdateBatch
.Close
End With
End Sub
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.