Access 97 'Error 3014 - Can't open any more tables.'

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