(Q) I'm getting "Error 3014 - Can't Open Any More Tables." What can I do to
solve it.
(A) The best solution is to download and install the Jet 3.5 Update:
Download Jet35Upd.Exe (SP3)
This doubles the number of "table references" from 1024 to 2048 and will make
a tremendous difference.
If you want to know more about this error, read on. BTW, this is all from Jet
3.5 documentation, I don't know about prior versions.
Error 3014 is a Jet error, not an Access error. Although the message refers
to opening "tables," the error is only roughly related to the actual number of
Access tables you have open. They are, instead, referring to Jet Table
References. Unfortunately, there is no good documentation describing the
frequency and quantity of Jet's use of these references. In short, you never can
determine when you're going to hit this error until you do.
You are allowed 1024 (2048 w/Jet upgrade) of these references. It is a fixed
limit and has nothing to do with available memory. Beefing up the machine will
not help. I have noticed that if memory is scarce (for example, other
applications are active) you will not get the Jet error message, you will get
the "MS Access has performed an illegal operation" message and Access will shut
down.
Here are 4 things that definitely impact the number of table references -
before replication enters the picture:
1. Open forms. The more tables a form's (or subform's) record source query
uses, the more table references are used. The more subforms appearing on a main
form, the more table references used - even if they are not visible. One trick
is to only assign a recordsource to a subform if the subform will be viewed (ex.
if you use tabs, set the record source OnClick of the tab). Requeries impact the
number of references.
2. List and combo boxes - eat up table references while the form is open.
3. Open recordset objects. Explicitly close them as soon as you are done.
4. Functions which use recordsets. Example - I had a function which opened a
table recordset to pull the one field of the one record in that table (stored a
date parameter). It closed the recordset as soon as the value was read. I was
calling this functions in many queries for subforms and pop-up forms. I found I
gained the ability to have many, many more table references when I called the
function only on my start up form and then referenced that form field everywhere
else.
Then replication comes into the picture. I have found no documentation
regarding this subject, but an application for which the table reference limit
had been worked around to perfection suddenly hit the limit much sooner when the
back end tables became replicas instead of "plain" data tables. I can only
assume that Jet keeps open a bunch of table references in order to manage the
replication process (i.e., determining when a record has changed, etc.)
Granted, this isn't definitive, but I do know from many hours of tweaking
that working on items 1 - 4 above will impact when you get the message.
Again, upgrading Jet makes the biggest difference of all!
