BeginTrans, CommitTrans, and RollbackTrans Methods Example (ADO)


		Contents Index Topic Contents

BeginTrans, CommitTrans, and 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.

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

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

			' Change the title for the specified
			' employee.
			If MsgBox(strMessage, vbYesNo) = vbYes Then
				rstTitles!Type = "self_help"
				rstTitles.Update
			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
		cnn1.CommitTrans
	Else
		cnn1.RollbackTrans
	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 data because this
	' is a demonstration.
	rstTitles.MoveFirst
	Do Until rstTitles.EOF
		If Trim(rstTitles!Type) = "self_help" Then
			rstTitles!Type = "psychology"
			rstTitles.Update
		End If
		rstTitles.MoveNext
	Loop

	rstTitles.Close
	cnn1.Close

End Sub