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

- Standard and Professional Editions of Microsoft Visual Basic

  for Windows, version 3.0

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.

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.

RESOLUTION

Open frequently used tables, dynasets, or snapshots outside the transaction.

STATUS

This behavior is by design.

MORE INFORMATION

Steps to Reproduce Behavior

1. Start a new project in Visual Basic. Form1 is created by default.

2. Add a command button (Command1) and a Label (Label1) to Form1.

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

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