ACC: Error Message: Can't Open Any More Tables (165272)
The information in this article applies to:
- Microsoft Access 2.0
- Microsoft Access for Windows 95 7.0
- Microsoft Access 97
This article was previously published under Q165272 Moderate: Requires basic macro, coding, and interoperability skills.
SYMPTOMS
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.
RESOLUTIONMethod 1
Obtain Jet35sp3.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:
172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
Method 2
Reduce the number of open recordsets, which includes the following:
- Tables
- Queries
- Bound forms and reports
- Combo boxes and list boxes whose row source 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 is much less likely to occur in Jet 3.5 SP2 and later. For more information about the latest Jet update, please see the
the following article in the Microsoft Knowledge Base:
172733 ACC97: Updated Version of Microsoft Jet 3.5 Available for Download
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.
| Modification Type: | Major | Last Reviewed: | 5/9/2003 |
|---|
| Keywords: | kberrmsg kbprb kbusage KB165272 |
|---|
|