ResetTrans Method Example

This example demonstrates the use of BeginTrans and ResetTrans to group a set of dynaset edits into a single transaction. 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

'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.

OraDynaset.Session.BeginTrans

'Traverse until EOF is reached, setting

'each employee's salary to zero.

Do Until OraDynaset.EOF

OraDynaset.Edit

OraDynaset.Fields("sal").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.

'Using ResetTrans means the rollback cannot

'be canceled in the Validate event.

OraDynaset.Session.ResetTrans

MsgBox "Salary changes rolled back."

End Sub