ACC: Error Message "Can't open any more tables"

ID: Q165272

The information in this article applies to:

Microsoft Access versions 2.0, 7.0, 97

SYMPTOMS

Moderate: Requires basic macro, coding, and interoperability skills.

When you try to open a table, a query, a form, or a report, or when you try to run Visual Basic for Applications code that uses recordsets, you may receive the following error message.

In Microsoft Access 7.0 and 97
------------------------------

Run-time error '2956':
Can't open any more tables.

In Microsoft Access 2.0
-----------------------

Run-time error '3037':
Can't open any more tables or queries

NOTE: This error may occur in a Microsoft Access 97 database that was converted from an earlier version of Microsoft Access.

CAUSE

You have exceeded the maximum number of open TableIDs allowed by the Microsoft Jet database engine.

RESOLUTION

Method 1

Obtain Jet35SP2.exe from the Microsoft Web site; this file contains the updated version 3.51 of the Microsoft Jet Database Engine.

For information on how to obtain Microsoft Jet 3.51, please see the the following article in the Microsoft Knowledge Base:

Q172733 ACC97: Updated Version of Microsoft Jet 3.5 Available on MSL

Method 2

Reduce the number of open recordsets, which includes the following:

tables

queries

bound forms and reports

combo boxes and list boxes whose RowSource is a table or query

Data Access Objects (DAO) recordsets in code

NOTE: When you create Recordset or QueryDef objects in code, explicitly close the object when you are finished with it. Microsoft Access automatically closes these types of objects under most circumstances; however, if you explicitly close the object in your code, you can avoid occasional instances when the object remains open.

STATUS

This behavior is by design.

MORE INFORMATION

The Microsoft Jet database engine versions 3.0 and 3.5 allow a maximum of 1024 open TableIDs at one time. Microsoft Jet version 3.51 allows a maximum of 2048 open TableIDs at one time. A TableID is a recordset pointer that the database engine uses, and which is not exposed to the user. The Jet database engine may open multiple TableIDs during the execution of a single query; however, there is no accurate method to estimate the number of TableIDs that the Jet database engine will use for any given operation.

For some operations, Microsoft Access 97 opens a few more TableIDs than do earlier versions of Microsoft Access. That is why you may encounter this error in a converted database that, for example, simultaneously opens multiple complex forms or reports. If you were already close to the TableID limit in Microsoft Access 2.0 or 7.0, the small amount of additional overhead in Microsoft Access 97 may be enough to exceed 1024 open TableIDs.

Additional query words: table id


Keywords          : kberrmsg kbusage CnvOthr 
Version           : WINDOWS:2.0,7.0,97
Platform          : WINDOWS 
Issue type        : kbprb

Last Reviewed: June 2, 1999