URGENT: Cannot Open Any More Tables

[Bill Sturdevant]

I keep getting error 3014 "Cannot open any more tables"
even though I only seem to have a few open, and in any
case, certainly less than the 2048 limit.

In Microsoft Knowledge Base Article 304536, it
says, "Microsoft has confirmed that this is a problem
in " ODBC Drive for Access 4.0 and OLE DB Provider for
Jet 4.0.

They say to install Jet 4.0 Service Pack 6. I am at
Service Pack 8.

What am I not doing? How can I be sure I have all the
right software installed?

 

[Sandra Daigle]

Hi Bill,

This error is a tiny bit misleading. Here is the KB article that gives a
better description of the real problem:

ACC: Error Message: Can't Open Any More Tables
http://support.microsoft.com/default.aspx?scid=kb;en-us;165272

You are actually running out of TableIds - this can easily happen when you
consider the fact that for every table referenced by a query you are using a
tableid. If the table is linked, my understanding is that it uses 2
tableids. Now look at all the queries that are used on a single form for the
form's recordsource, rowsources of combos and listboxes, recordsources for
subforms and their combos and lists, etc. I don't know that there is a way
for you to anticipate reaching the limit (I hope someone will tell me if
there is).

The way to avoid the error is to simplify the forms that can be open
simultaneously. The biggest hitter is ususally a form with a tab control
containing multiple subforms. This is easily remedied by unbinding the
subforms from their sourceobjects and binding them only when needed by using
the Change event of the tab control.