ACC1x: Opening Objects in Transactions Can Cause Rollback

• Microsoft Access versions 1.0, 1.1

SYMPTOMS

The following error message is displayed while attempting to open a second occurrence of a database object (such as a table, dynaset, or form) in a transaction:

   Invalid database object

CAUSE

This error message results from a new occurrence of a database object overwriting a previous occurrence of a database object, which "orphans" the first object (that is, the first object's pointer to the database is destroyed).

When Microsoft Access detects that an object is orphaned, it attempts to close it. Because the object is in a transaction, however, the close fails, and therefore Microsoft Access rolls back all levels of nested transactions so that the close succeeds.

This implicit rollback causes all objects opened in transactions to be closed. As a result, any reference to an object closed by this process results in the "Invalid database object" error message.

RESOLUTION

This problem occurs only when objects are implicitly closed (by the process mentioned above), and does not occur when the object is explicitly closed in a transaction.

Thus, the keys to avoiding this problem are:

• Always open objects outside of transactions.
• Always explicitly close objects.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 1.0 and 1.1. This problem no longer occurs in Microsoft Access version 2.0.

MORE INFORMATION

Steps to Reproduce Problem

Create and run the following procedures:

   Sub Sub1()
     Dim db As Database, tb As Table

     Set db = CurrentDB()
     BeginTrans
        Set tb = db.OpenTable("table1") 'Table opened in transaction
        Call Sub2
        Debug.Print tb.recordcount   'Result: "! Invalid database
                                     'object."
        tb.Close
     CommitTrans
     db.Close
   End Sub

   Sub Sub2()
     Dim d as Database
     Set d = CurrentDB()
   End Sub  'd loses scope, does implicit rollback and close of db.