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.
In this tutorial:
- Using DAO to Access Data
- Data Access Objects
- New Features in DAO
- Referring to DAO Objects
- The DBEngine Object
- Using Transactions
- The Errors Collection
- The Databases Collection
- The CurrentDb() Function
- Opening an External Database
- Closing and Destroying Database Object References
- DAO Property Types
- Setting and Retrieving Built-In Object Properties
- Setting and Retrieving SummaryInfo Properties
- Creating Schema Objects with DAO
- Creating Indexes
- Creating Relations
- Creating Multi-Value Lookup Fields
- Database Encryption with DAO
- Setting Encryption Options
- Managing Access (JET) Security with DAO
- Creating Security Objects
- Creating and Deleting Groups
- Managing Passwords
- Data Access with DAO
- Modifying a QueryDef
- Filtering and Ordering Recordsets
- Navigating Recordsets
- BOF, EOF
- Navigating Recordsets with Multi-Value Lookup Fields
- Bookmarks and Recordset Clones
- Finding Records
- Working with Recordsets
- Using Arrays with Recordsets
- Working with Attachment Fields
- Append Only Fields