BeginTrans, CommitTrans, Rollback Statements
BeginTransbegins a new transaction .
CommitTransends the current transaction.
Rollbackends the current transaction and restores the database to the state it was in just before the current transaction began.
A transaction is a series of changes you make to a database that you want to treat as one complete unit. A transaction begins when you use the BeginTrans statement. Use Rollback to undo changes made during the current transaction, and CommitTrans to accept changes and end the current transaction. Both Rollback and CommitTrans end a transaction. Once you use CommitTrans, you can't undo changes made during that transaction. You can have up to five levels of transactions open at once by using multiple BeginTrans statements.
Typically, you use transactions to maintain the integrity of your data when records in two or more tables must be updated. For example, if you transfer money from one account to another, you might subtract a sum from one and add the sum to another. If either update fails, the accounts no longer balance. Use BeginTrans before updating the first record, and then if any subsequent update fails, you can use Rollback to undo all of the updates. Use CommitTrans after the last record has been successfully updated.
Note Some databases, such as Paradox, may not support transactions, in which case the Transactions property of the Database object is False. Test the value of the Transactions property before using BeginTrans to make sure that the Database supports transactions. If transactions are not supported, these statements are ignored and no error occurs.
If you use CommitTrans or Rollback statements without first using BeginTrans, an error occurs.
If you use Rollback, you should use Refresh on any data control that refers to data that may have changed since the transaction began.
Caution Whether you use one of these statements or their corresponding methods, the effect is the same. Transactions are always global and never limited to only one database or recordset. If you include operations on more than one database or recordset within a transaction, Rollback restores all operations on all databases.