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.