Visual Basic: RDO Data Control

  • Article

BeginTrans, CommitTrans, RollbackTrans Methods (Remote Data)

See Also    Example    Applies To

The transaction methods manage transaction processing during a session represented by the object placeholder as follows:

  • BeginTrans begins a new transaction.

  • CommitTrans ends the current transaction and saves the changes.

  • RollbackTrans ends the current transaction and restores the databases in the rdoEnvironment object to the state they were in when the current transaction began.

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:

  • Auto-commit Mode: When you have not explicitly started a transaction using the BeginTrans method, every operation executed is immediately committed to the database upon completion.

  • Manual-commit Mode: When you explicitly start a transaction using the BeginTrans method or use the ODBC SQLSetStmtOption function to disable the SQL_AUTO_COMMIT mode, or send an SQL statement to begin a transaction (BEGIN TRANS), operations are part of a transaction and no changes are committed to the database until you use the CommitTrans method. If the connection fails before CommitTrans is executed, or you use the RollbackTrans method, the operations are undone rolled back.

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 updaterows 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 arent 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 cant 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 doesnt support transactions, the methods are ignored and no error occurs.