PRB: Compatibility Layer Error: Can't open any more tables (116034)

SYMPTOMS

If you have the Microsoft Jet 2.0/Visual Basic 3.0 Compatibility Layer installed on your computer, and you perform a transaction in which you open multiple tables, dynasets, or snapshots, you may encounter error 3014:

Can't open any more tables.

This occurs after your transaction opens approximately three hundred tables, dynasets, or snapshots. The error occurs even if you explicitly close the tables, dynasets, or snapshots at some other point during the transaction.

CAUSE

This behavior is the result of a change to the Microsoft Access (Jet) database engine. In the Jet version 1.1 database engine, tables could be closed during a transaction. This resulted in unexpected behavior where closed tables would either be omitted from the transaction or cause an implicit RollBack.

This problem was resolved in the Jet version 2.0 database engine by keeping table references open until the transaction finishes, so tables closed during the transaction can be maintained as part of the transaction without causing a RollBack. However, because table references are maintained, it is possible to exhaust the database engine's workspace if you open a large number of tables during the transaction.

MORE INFORMATION

Steps to Reproduce Behavior

Start a new project in Visual Basic. Form1 is created by default.
Add a command button (Command1) and a Label (Label1) to Form1.

Place the following code in the Command1 Click event:

   Sub Command1_Click ()
      Dim i  As Integer
      Dim db As database
      Dim ss As snapshot

      On Error GoTo Command1_ClickError

      Set db = OpenDatabase("c:\vb\biblio.mdb")
      BeginTrans

      For i = 1 To 500
         Label1.Caption = i
         Label1.Refresh
         Set ss = db.CreateSnapshot("SELECT * FROM Authors")
         '
         ' Code to manipulate the Snapshot records would be here ...
         '
         ss.Close
         Set ss = Nothing
      Next i

      Rollback
      db.Close

      Label1.Caption = Trim(Label1.Caption) & " Done!"
      Label1.Refresh
   Exit Sub

   Command1_ClickError:
      If Err <> 0 Then
         ' Enter the following two lines as one, single line:
         MsgBox "Error: " & Trim(CStr(Err)) & Chr$(10) & Chr$(13)
              & "Error: " & Trim(Error$)
         Label1.Caption = Trim(Label1.Caption) & " FAIL!"
         Label1.Refresh
         Rollback
      End If
      Exit Sub
   End Sub

Run the program. The application will run through 332 iterations of creating and closing the snapshot before generating the error.

Code to Avoid the Error

To avoid the error, rewrite the code for the loop as:

   Set ss = db.CreateSnapshot("SELECT * FROM Authors")
   For i = 1 To 500
      Label1.Caption = i
      Label1.Refresh
      '
      ' Code to manipulate the Snapshot records would be here ...
      '
   Next i
   ss.Close
   Set ss = Nothing

Because the snapshot is opened outside the loop, only one reference to the underlying tables is created, so the "Can't open any more tables" error does not occur.