[AccessD] Error 3014 Cannot open any more tables

[Janet Erbach]

Thank you, all of you, for your prompt replies!  I'm usually very good
about closing recordsets, but I will look very closely for something I
missed.  And Rocky, I like your idea of running some code to tell me what's
open.  And Dan, the static variable is a good idea too.  I will get back to
you all and let you know what I find!

Janet

On Wed, Apr 26, 2017 at 5:31 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
>
>  Locks yes, but tables no.   Access has internal limits on that which
> cannot
> be modified.  Won't hurt to try more locks though.
>
>   I'm with John on this; sounds like something is not getting closed.
> Anything opened in code should be explicitly being closed and object
> variables set to nothing.  I'd also look for any procedure that makes a
> recursive call.   It may be that the process is simply too much.
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Rocky Smolin
> Sent: Tuesday, April 25, 2017 06:55 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Error 3014 Cannot open any more tables
>
> Is there a registry key that can be increased like the MaxLocksPerFile
> hack:
>
> https://support.microsoft.com/en-us/help/815281/-file-
> sharing-lock-count-exc
> eeded-error-message-during-large-transaction-processing
>
> http://tinyurl.com/l77zk7f
>
> to allow more tables?  Although John's probably right - you probably don't
> need that many tables open.
>
> Can you trap that error and run a few lines of code which would list all
> the
> open tables? Maybe you could spot the culprit?
>
> R
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Janet Erbach
> Sent: Tuesday, April 25, 2017 2:39 PM
> To: Database Advisors
> Subject: [AccessD] Error 3014 Cannot open any more tables
>
> Hello, all.
>
> I deployed a 2007 app today that is supposed to be up and running all the
> time.  It's relatively simple - one form bound to a temporary table that is
> emptied and refilled every minute on the form timer event.
>
> After about 30 minutes, the app throws a 3014 Error - cannot open any more
> tables.
>
> We are running jet 4.0, which is the most current, yes?  If this is truly a
> jet related error, there are a couple of things I can do to help optimize
> the app - about half the queries in the app are run from saved query defs,
> and I can convert all those to sql statements.  I've decompiled and
> reinstalled the access database engine, both suggestions that turned up in
> a
> google search.  But beyond these troubleshooting steps, I don't know what
> else to do.
>
> Can you please give me some clues on what may be triggering this?  I
> haven't
> seen this error before (that I can remember, anyway) and I have another app
> deployed on the shop floor which runs 24/7 and refreshes every minute
> without any issues.  Any help is greatly appreciated!
>
> Janet Erbach
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>