The CommitTrans method acts differently for these objects:
OraConnection and OraDatabase
The CommitTrans method commits all pending transactions for the specified connection. This method has no effect if a transaction has not started. When a sessionwide transaction is in progress, you can use this method to commit the transactions for the specified connection prematurely.
OraSession
The CommitTrans method commits all transactions present within the session. The CommitTrans method is valid only when a transaction has been started. If a transaction has not been started, using the CommitTrans method causes an error.
Note: If an OraDatabase object has been enlisted with Microsoft Transaction Server (MTS) and is part of a global MTS transaction, this method has no effect.
This example demonstrates the use of the BeginTrans method to group a set of dynaset edits into a single transaction. The CommitTrans method then accepts the changes. Copy this code into the definition section of a form. Then, press F5.
Sub Form_Load ()
'Declare variables
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim fld As OraField
'Create the OraSession Object.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")
'Create the OraDatabase Object by opening a connection to Oracle.
Set OraDatabase = OraSession. OpenDatabase("ExampleDb", "scott/tiger", 0&)
'Create the OraDynaset Object.
Set OraDynaset = OraDatabase.CreateDynaset("select * from emp", 0&)
'Start Transaction processing.
OraSession.BeginTrans
'Setup a field object to save object references.
Set fld = OraDynaset.Fields("sal")
'Traverse until EOF is reached, setting each employees salary to zero.
Do Until OraDynaset.EOF = True
OraDynaset.Edit
fld.value = 0
OraDynaset.Update
OraDynaset.MoveNext
Loop
MsgBox "All salaries set to ZERO."
'Currently, the changes have NOT been committed
'to the database.
'End Transaction processing. Commit the changes to the database
OraSession.CommitTrans
MsgBox "Salary changes committed."
End Sub