PRB: Commit or Rollback without BeginTrans Error and VB Forms

MORE INFORMATION

BeginTrans, CommitTrans, and Rollback Statements

To perform database transactions in Visual Basic, you can use the BeginTrans, CommitTrans, Rollback statements. BeginTrans begins a new transaction. CommitTrans ends the current transaction. Rollback ends 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 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.

The following are some suggestions when using transactions which can help prevent implicit CommitTrans:

• Keep transaction processing loops as short as possible, especially in a multi-user system.
• Avoid loading forms, showing a loaded form, or moving to a form during a transaction. While a form with a data control on it can create an implicit CommitTrans (as documented above), it is wise to minimize the amount of form activity going on while within a transaction.
• Avoid dimensioning data access object variables during a transaction.
• Avoid performing any Open or Close methods on database objects (Database, Table, Dynaset or Snapshot) during a transaction.
• Avoid performing a Create... method or function during transaction processing.
• Avoid invoking a refresh method on a database object or data control within a transaction. It may generate an implicit CommitTrans.

Steps to Reproduce Behavior

1. Start a new project in Visual Basic. Form1 is created by default.
2. Add a data control (Data1) to Form1.
3. Connect Data1 on Form1 to a table in a database as follows:
   
   Select the Data1 control and press the F4 key to display the Properties window. Set the DatabaseName property to C:\VB3\BIBLIO.MDB, and set the RecordSource property to the source table name Publishers.
4. From the File menu, choose New Form to create Form2.
5. Add a data control (Data1) to Form2.
6. Connect Data1 on Form2 to any table in any database (the same or different database than on Form1) as follows:
   
   Select the Data1 control and press the F4 key to display the Properties window. Set the DatabaseName property to C:\ACCESS\NWIND.MDB, and set the RecordSource property to the source table name Categories.
7. Add the following code to the Form1 Load event:

      Sub Form_Load ()
         BeginTrans  ' Begin the transaction.
         Form1.Show
         Form2.Show  ' Show Form2 on top of Form1.
      End Sub
   
      Sub Form_Unload (Cancel As Integer)
         CommitTrans   ' This statement causes an error.
      End Sub
   						

8. Start the program, or press the F5 key.
9. Close Form2.
10. Close Form1. This results in the following error message:
    Commit or Rollback without BeginTrans (Err = 3034)

In this example, the error you get when unloading Form1 is actually caused by unloading Form2.

When Form1 loads, Visual Basic automatically invokes the Data1.Refresh method for the attached data control. That automatically opens the specified database and table. When Form2 loads, the same behavior occurs to open the second database and table.

As Form2 unloads, Form2 checks to see if the data control's database is still open. If the database is still open, Visual Basic automatically does a Rollback and closes the database in order to cancel any unsaved changes to the current record in the data control. This default behavior often saves you from writing extra code. That automatic Rollback cancels the BeginTrans that you invoked in the Form1 Load event. As Form1 unloads, the CommitTrans in the form's unload event has no transaction to commit, so you get the error message.

To work around this behavior, add a Data1.Database.Close method in the Unload event for Form2 to prevent the automatic Rollback.