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
Top of Page
© 1997 Microsoft Corporation. All rights reserved. Terms of Use.