The transaction methods manage transaction processing during a session represented by the object placeholder as follows:
You can use the transaction methods with an rdoConnection object — but in this case, the transaction scope only includes rdoResultset and rdoQuery objects created under the rdoConnection.
Syntax
object.BeginTrans | CommitTrans | RollbackTrans
The object placeholder represents an object expression that evaluates to an object in the Applies To list.
Remarks
You use the transaction methods with an rdoEnvironment or rdoConnection object when you want to treat a series of changes made to the databases in a session as one logical unit. That is, either the set of operations completes as a set, or is rolled back as a set. This way if any operation in the set fails, the entire transaction fails. Transactions also permit you to make temporary changes to the database – changes that can be undone with the RollbackTrans method.
Typically, ODBC drivers work in one of two modes:
Note When working with remote servers that support a Distributed Transaction Coordinator (DTC) like Microsoft SQL Server, you can initiate and control transactions that span more than one server. That is, if you invoke a procedure on the remote server that invokes a remote procedure call, the DTC service can ensure that this operation is included in the initial transaction.
Typically, you use transactions to maintain the integrity of your data when you must update rows in two or more tables and ensure that changes made 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 row, and then, if any subsequent update fails, you can use the RollbackTrans method to undo all of the updates. Use the CommitTrans method after you successfully update the last row.
Caution Within one rdoEnvironment object, transactions are always global to the rdoEnvironment and aren’t limited to only one database or result set. If you perform operations on more than one database or result set within an rdoEnvironment transaction, the RollbackTrans method restores all operations on those databases and result sets.
Once 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. You cannot nest transactions unless you use an action query to directly execute SQL transaction management statements. If you want to have simultaneous transactions with overlapping, non-nested scopes, you can create additional rdoEnvironment objects to contain the concurrent transactions.
Note You can use SQL action queries that contain transaction statements. For example, with Microsoft SQL Server, you can use SQL statements like BEGIN TRANSACTION, COMMIT TRANSACTION, or ROLLBACK TRANSACTION. This technique supports nested transactions which may not be supported by the ODBC driver.
If you close an rdoEnvironment object without saving or rolling back any pending transactions, the transactions are automatically rolled back.
No error occurs If you use the CommitTrans or RollbackTrans method without first using the BeginTrans method.
Some databases may not support transactions, in which case the Transactions property of the rdoConnection object or rdoResultset object is False. To make sure that the database supports transactions, check the value of the Transactions property of the rdoConnection object before using the BeginTrans method. If you are using an rdoResultset object based on more than one database, check the Transactions property of the rdoResultset object. If the rdoConnection or rdoResultset doesn’t support transactions, the methods are ignored and no error occurs.