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.