BeginTrans, CommitTrans, RollbackTrans Methods Example

This example changes the book type of all psychology books in the titles table of the database. After the BeginTrans method starts a transaction that isolates all the changes made to the Titles table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved using the Update method.

Sub BeginTransX()

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

	' Open connection.
	strCnn = "driver={SQL Server};server=srv;" & _
		"uid=sa;pwd=;database=pubs"
	Set cnn1 = New ADODB.Connection
	cnn1.Open strCnn

	' Open titles table.
	Set rstTitles = New ADODB.Recordset
	rstTitles.CursorType = adOpenDynamic
	rstTitles.LockType = adLockPessimistic
	rstTitles.Open "titles", cnn1
	
	With rstTitles
		.MoveFirst
		cnn1.BeginTrans

		' 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?"

				' Change the title for the specified employee.
				If MsgBox(strMessage, vbYesNo) = vbYes Then
					!Type = "self help"
					.Update
				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
			cnn1.CommitTrans
		Else
			cnn1.RollbackTrans
		End If

		' Print current data in recordset.
		.Requery
		.MoveFirst
		Do While Not .EOF
			Debug.Print !Title & " - " & !Type
			.MoveNext
		Loop
		
		' Restore original data because this
		' is a demonstration.
		.MoveFirst
		Do Until .EOF
			If Trim(!Type) = "self_help" Then
				!Type = "psychology"
				.Update
			End If
			.MoveNext
		Loop
		.Close
	End With

	cnn1.Close

End Sub

© 1997 Microsoft Corporation. All rights reserved. Terms of Use.