|
(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!
|
