BeginTrans, CommitTrans, and RollbackTrans Methods Example (VB)

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. You can use the RollbackTrans method to undo changes that you saved using the Update method.

'BeginBeginTransVB

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string
    
Public Sub BeginTransX()

    'recordset and connection variables
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim rstTitles As ADODB.Recordset
   Dim strSQLTitles As String
    'record variables
   Dim strTitle As String
   Dim strMessage As String

   ' Open connection
      strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Set Cnxn = New ADODB.Connection
   Cnxn.Open strCnxn

   ' Open recordset dynamic to allow for changes
   Set rstTitles = New ADODB.Recordset
   strSQLTitles = "Titles"
   rstTitles.Open strSQLTitles, Cnxn, adOpenDynamic, adLockPessimistic, adCmdTable
   
   Cnxn.BeginTrans

   ' Loop through recordset and prompt user
   ' to change the type for a specified title
   
   rstTitles.MoveFirst
   
   Do Until rstTitles.EOF
      
     If Trim(rstTitles!Type) = "psychology" Then
         strTitle = rstTitles!Title
         strMessage = "Title: " & strTitle & vbCr & _
         "Change type to self help?"

         ' If yes, change type for the specified title
         If MsgBox(strMessage, vbYesNo) = vbYes Then
            rstTitles!Type = "self_help"
            rstTitles.Update
         End If
         
      End If
   rstTitles.MoveNext
   Loop

   ' Prompt user to commit all changes made
   If MsgBox("Save all changes?", vbYesNo) = vbYes Then
      Cnxn.CommitTrans
   Else
      Cnxn.RollbackTrans
   End If

   ' Print recordset
   rstTitles.Requery
   rstTitles.MoveFirst
   Do While Not rstTitles.EOF
      Debug.Print rstTitles!Title & " - " & rstTitles!Type
      rstTitles.MoveNext
   Loop

   ' Restore original data as this is a demo
   rstTitles.MoveFirst
   
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "self_help" Then
         rstTitles!Type = "psychology"
         rstTitles.Update
      End If
   
   rstTitles.MoveNext
   Loop
   
    ' clean up
   rstTitles.Close
   Cnxn.Close
   Set rstTitles = Nothing
   Set Cnxn = Nothing

End Sub


'EndBeginTransVB

BeginTrans, CommitTrans, and RollbackTrans Methods Example (VB)

See Also