BeginTrans, CommitTrans, Rollback Methods Example (MDB)

The following example changes the job title of all sales representatives in an Employees table. After the BeginTrans method starts a transaction that isolates all the changes made to the Employees table, the CommitTrans method saves the changes. Notice that you can use the Rollback method to undo changes that you saved by using the Update method. The example also shows how to maintain a flag to properly manage errors that might occur during a transaction.

One or more table pages remain locked while the user decides whether or not to accept the changes. Consequently, this technique isn't recommended but is shown only as an example.

Sub ChangeTitle()
    Dim wsp As Workspace, dbs As Database, rst As Recordset
    Dim strName As String, strMessage As String, _
        strPrompt As String
    Dim fInTrans As Boolean

    On Error GoTo ChangeTitleErr

    fInTrans = False
    strPrompt = "Change title to Account Executive?"
    ' Return reference to default Workspace object.
    Set wsp = DBEngine.Workspaces(0)
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Create table-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
    ' Start of transaction.
    wsp.BeginTrans
    fInTrans = True
    rst.MoveFirst
    Do Until rst.EOF
        If rst!Title = "Sales Representative" Then
            strName = rst!LastName & ", " & rst!FirstName
            strMessage = "Employee: " & strName & vbCrLf & vbCrLf
            If MsgBox(strMessage & strPrompt, vbQuestion + _
                    vbYesNo, "Change Job Title") = vbYes Then
                ' Enable editing.
                rst.Edit
                rst!Title = "Account Executive"
                ' Save changes.
                rst.Update
            End If
        End If
        ' Move to next record.
        rst.MoveNext
    Loop
    If MsgBox("Save all changes?", vbQuestion + vbYesNo, _
            " Save Changes") = vbYes Then
        wsp.CommitTrans            ' Commit changes.
    Else
        wsp.Rollback                ' Undo changes.
    End If
ChangeTitleExit:
    rst.Close
    Set dbs = Nothing
    Set wsp = Nothing
    Exit Sub
ChangeTitleErr:
    MsgBox "Error!"
    If fInTrans Then
        wsp.Rollback
    End If
    Resume ChangeTitleExit
End Sub