Using Transactions

A transaction is defined as a delimited set of changes that are performed on a database's schema or data. They increase the speed of actions that change data, and enable you to undo changes that have not yet been committed.

Transactions offer a great deal of data integrity insurance for situations where an entire series of actions must complete successfully, or not complete at all. This is the all-or-nothing principle that is employed in most financial transactions.

For example, when your employer transfers your monthly salary from their bank to yours, two actions actually occur. The first is a withdrawal from your employer's account, and the second is a deposit into yours. If the withdrawal completes, but for some reason, the deposit fails, you can argue until you're blue in the face, but your employer can prove that they paid you, and are not likely to want to do so again. Similarly, your bank will not be too impressed if the withdrawal fails, but the deposit succeeds. The reality is that the bank will take the money back, and you still end up with no salary. If, however, the two actions are enclosed in a single transaction, they must both complete successfully, or the transaction is deemed to have failed, and both actions are rolled back (reversed).

You begin a transaction by issuing the BeginTrans method against the Workspace object. To write the transaction to disk, you issue the CommitTrans method, and to cancel, or roll back the transaction, strangely enough, you issue the Rollback method.

Normally, transactions are cached, and not immediately written to disk. But if you're in a real hurry to get home at five o'clock, and immediately switch off your computer before the cache is written to disk, your most recent changes are lost. In Microsoft Access workspaces, you can force the database engine to immediately write all changes to disk, instead of caching them. You do this by including the dbForceOSFlush constant with CommitTrans. Forcing immediate writes may affect your application's performance, but the data integrity benefits may outweigh any performance hit in certain situations.

The following code segment demonstrates a typical funds transfer transaction. In this and in other examples in this tutorial, the code deviates from the Reddick object-naming convention by varying the names for Workspace, Database, and Recordset object variables, making the code easier to understand. In this example, rather than extend the length of the two Database object names, they are named dbC and dbX, for the current and external databases respectively. They could just as easily have been named dbsC and dbsX.

Public Sub TransferFunds()
    Dim wrk As DAO.Workspace
    Dim dbC As DAO.Database
    Dim dbX As DAO.Database

    Set wrk = DBEngine(0)
    Set dbC = CurrentDb
    Set dbX = wrk.OpenDatabase("c:\Temp\myDB.mdb")

    On Error GoTo trans_Err

    'Begin the transaction
    wrk.BeginTrans

    'Run a SQL statement to withdraw funds from one account table
    dbC.Execute "UPDATE Table1.....", dbFailOnError

    'Run a SQL statement to deposit funds into another account table
    dbX.Execute "INSERT INTO Table22.....", dbFailOnError

    'Commit the transaction
    wrk.CommitTrans dbForceOSFlush

trans_Exit:
    'Clean up
    wrk.Close
    Set dbC = Nothing
    Set dbX = Nothing
    Set wrk = Nothing
    Exit Sub

trans_Err:
    'Roll back the transaction
    wrk.Rollback
    Resume trans_Exit
End Sub

In this example, changes to both databases will complete as a unit, or will be rolled back as a unit.

You don't need to use transactions, but if you do, they can be nested up to five levels. It is also important to understand that transactions are global to the workspace - not the database. For example, if you make changes to two databases in the same workspace, and you roll back the changes to one of those databases, the changes made to the other database will also be rolled back.