BeginTrans, CommitTrans, Rollback Methods

Applies To DBEngine object, Workspace object.

Description

The transaction methods manage transaction processing during a session defined by a Workspace object as follows:

BeginTrans begins a new transaction.
CommitTrans ends the current transaction and saves the changes.
Rollback ends the current transaction and restores the databases in the Workspace object to the state they were in when the current transaction began.

Syntax

workspace.BeginTrans | CommitTrans [dbFlushOSCacheWrites] | Rollback

The workspace placeholder is an object variable that represents the Workspace containing the databases that will use transactions.

Remarks

You use these methods with a Workspace object when you want to treat a series of changes made to the databases in a session as one unit.

Typically, you use transactions to maintain the integrity of your data when you must both update records in two or more tables and ensure changes are completed (committed) in all tables or none at all (rolled back). For example, if you transfer money from one account to another, you might subtract an amount from one and add the amount to another. If either update fails, the accounts no longer balance. Use the BeginTrans method before updating the first record, and then, if any subsequent update fails, you can use the Rollback method to undo all of the updates. Use the CommitTrans method after you successfully update the last record.

In a Microsoft Jet workspace, you can include the dbFlushOSCacheWrites constant with CommitTrans, This forces the database engine to immediately flush all updates to disk, instead of caching them temporarily. Without using this option, a user could get control back immediately after the application program calls CommitTrans, turn the computer off, and not have the data written to disk. While using this option may affect your application's performance, it is useful in situations where the computer could be shut off before cached updates are saved to disk.

Caution Within one Workspace object, transactions are always global to the Workspace and aren't limited to only one Connection or Database object. If you perform operations on more than one connection or database within a Workspace transaction, resolving the transaction (that is, using the CommitTrans or Rollback method) affects all operations on all connections and databases within that workspace.

After you use CommitTrans, you can't undo changes made during that transaction unless the transaction is nested within another transaction that is itself rolled back. If you nest transactions, you must resolve the current transaction before you can resolve a transaction at a higher level of nesting.

If you want to have simultaneous transactions with overlapping, non-nested scopes, you can create additional Workspace objects to contain the concurrent transactions.

If you close a Workspace object without resolving any pending transactions, the transactions are automatically rolled back.

If you use the CommitTrans or Rollback method without first using the BeginTrans method, an error occurs.

Some ISAM databases used in a Microsoft Jet workspace may not support transactions, in which case the Transactions property of the Database object or Recordset object is False. To make sure the database supports transactions, check the value of the Transactions property of the Database object before using the BeginTrans method. If you are using a Recordset object based on more than one database, check the Transactions property of the Recordset object. If a Recordset is based entirely on Microsoft Jet tables, you can always use transactions. Recordset objects based on tables created by other database products, however, may not support transactions. For example, you can't use transactions in a Recordset based on a Paradox table. In this case, the Transactions property is False. If the Database or Recordset doesn't support transactions, the methods are ignored and no error occurs.

You can't nest transactions if you are accessing ODBC data sources through the Microsoft Jet database engine.

Notes

You can often improve the performance of your application by breaking operations that require disk access into transaction blocks. This buffers your operations and may significantly reduce the number of times a disk is accessed.
In a Microsoft Jet workspace, transactions are logged in a file kept in the directory specified by the TEMP environment variable on the workstation. If the transaction log file exhausts the available storage on your TEMP drive, the database engine triggers a run-time error. At this point, if you use CommitTrans, an indeterminate number of operations are committed, but the remaining uncompleted operations are lost, and the operation has to be restarted. Using a Rollback method releases the transaction log and rolls back all operations in the transaction.

See Also

Close method, CreateWorkspace method, Refresh method, Transactions property.

Example

This example changes the job title of all sales representatives in the Employees table of the database. 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 using the Update method. Furthermore, the main transaction is nested within another transaction that automatically rolls back any changes made by the user during this example.

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

Sub BeginTransX()

    Dim strName As String
    Dim strMessage As String
    Dim wrkDefault As Workspace
    Dim dbsNorthwind As Database
    Dim rstEmployees As Recordset

    ' Get default Workspace.
    Set wrkDefault = DBEngine.Workspaces(0)
    Set dbsNorthwind = OpenDatabase("Northwind.mdb")
    Set rstEmployees = dbsNorthwind.OpenRecordset("Employees")

    ' Start of outer transaction.
    wrkDefault.BeginTrans
    ' Start of main transaction.
    wrkDefault.BeginTrans

    With rstEmployees

        ' Loop through recordset and ask user if she wants to
        ' change the title for a specified employee.
        Do Until .EOF
            If !Title = "Sales Representative" Then
                strName = !LastName & ", " & !FirstName
                strMessage = "Employee: " & strName & vbCr & _
                    "Change title to Account Executive?"

                ' Change the title for the specified employee.
                If MsgBox(strMessage, vbYesNo) = vbYes Then
                    .Edit
                    !Title = "Account Executive"
                    .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
            wrkDefault.CommitTrans
        Else
            wrkDefault.Rollback
        End If

        ' Print current data in recordset.
        .MoveFirst
        Do While Not .EOF
            Debug.Print !LastName & ", " & !FirstName & _
                " - " & !Title
            .MoveNext
        Loop
        ' Roll back any changes made by the user since this is
        ' a demonstration.
        wrkDefault.Rollback
        .Close
    End With

    dbsNorthwind.Close

End Sub

Example (Microsoft Access)

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