Microsoft KB Archive/116034

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

Article ID: 116034

Article Last Modified on 10/29/2003

-

APPLIES TO


 * Microsoft Visual Basic 3.0 Professional Edition
 * Microsoft Visual Basic 3.0 Professional Edition

-



This article was previously published under Q116034



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.



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



STATUS
This behavior is by design.



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.

Additional query words: 3.00 errmsg

Keywords: kbprb KB116034

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.