INCREASING SPEED WITH TRANSACTIONS
In the CopyData subroutine in Listing 5.10, notice the wspTransact.BeginTrans line. Access supports transactions, which, in some cases, greatly increase the speed of modifying and updating data. By using a transaction, you can group a large number of updates into a single operation.
However, transactions are more than just faster updates. By using transactions, you can modify one or many tables. At any point during the update, you can decide to cancel, or roll back, the transaction. Rolling back a transaction causes no updates to be saved to your database.
Note
If you plan to use transactions for performance purposes, be sure to run benchmarks with and without the transaction commands. In prior versions of Jet and Access, transactions could be counted on for increasing speed; as of Access 97 and Jet 3.5, this wasn't always the case. With Access 97/2000 and Jet 4, the main purpose for using transactions should be to take advantage of the rollback capability, if necessary.
Using transactions can be a very efficient and effective way to perform bulk operations. If, during the bulk update, an error or unexpected condition occurs, you can roll back the transaction, leaving your database in its original state.
Caution
Although transactions might sound wonderful, you must be aware of many complexities. For every BeginTrans you call, you must always have a CommitTrans or Rollback. Leaving transactions open can cause unpredictable and possibly dangerous effects because you'll leave recordsets open with locks on them. This is especially important to recognize when debugging an application. If you stop your program in the middle of a transaction, you shouldn't reset your application without committing or rolling back the transaction. For example, in the CopyData subroutine in Listing 5.10, if you stopped the procedure with Ctrl+Break with the intention of aborting the procedure, you must execute the following line of code in the Immediate window:
wsTransact.Rollback
Omitting the rollback leaves your application in a potentially dangerous state, with recordsets left locked.
Transactions encompass any and all modifications and updates to any database that occur within the Workspace object. Therefore, you can create transactions that span multiple tables and even databases. This is important to understand, especially when you roll back, because you'll be rolling back all the actions that have occurred since the beginning of the transaction.
Transactions support the capability of being nested. This means you can create a transaction and within that transaction create another transaction. You can roll back the inside transaction and continue to process the outside transaction. You can also commit the inside transaction and then roll back the entire operation by rolling back the outside transaction.
Sometimes you may actually want to run two transactions independently and simultaneously. You can easily do so by creating a clone of your Workspace. On the Application object of Access, you can call the DefaultWorkspaceClone method to obtain a second Workspace on your database. By using this Workspace, you can initiate a second, independent transaction that can run at the same time as the transaction on the default Workspace.
Note
The DefaultWorkspaceClone method doesn't require the user to log back on. The cloned workspace has the same characteristics as the original workspace. It's equivalent to the user logging on a second time with the same password.