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, , , adCmdTable
	
	rstTitles.MoveFirst

	' Loop through recordset and ask user if she wants 
	' to change the 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 above.
	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