[AccessD] Error 3014 Cannot open any more tables

[Janet Erbach]

Jim - Thank you for this suggestion.  I may have to do this - the app ran
all night on my laptop (local copies of the data - NOT networked) and it
never crashed.  But on the shop floor it's crashing now after about 3
hours.  Better than 30 minutes...but still not cool.

I already  do this 'task scheduler' start up with a different access
application I created a couple of years ago, so I know what you're
describing.  I have a powershell script that is launched by the scheduler
which closes access if it's already open, and then launches a new instance
of the app.  I can do this the same way.

Do you think this problem could have anything to do with the fact that my
form is based on a temporary table which I'm emptying and refilling via
queries every 30 seconds?  I'm asking because I have another app on the
shop floor which runs all day long without ever generating this error...but
it's linked to a local table, and it's not being re-queried all day long.
I'm guessing that's got to be the reason behind this.

Janet

On Thu, Apr 27, 2017 at 5:24 AM, Jim Dettman <jimdettman at verizon.net> wrote:

>
> Janet,
>
>   There also may be an Access bug that your tripping over causing a
> resource
> leak.
>
>   Not sure what the process is, but consider not leaving it running
> constantly. Instead have it do it's process, then quit, and execute it
> again
> with the windows task scheduler when needed (every xx minutes).
>
>   If the process might run over the next startup, you can use code at app
> startup to prevent multiple instances running
> (https://www.experts-exchange.com/articles/2104/Avoiding-
> running-multiple-in
> stances-of-an-application.html )
>
>   This is the way I structure most of my automated tasks.  Starting over
> each time gives you a "clean slate" which I find a more robust solution.
>
> Jim.
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
> Janet Erbach
> Sent: Wednesday, April 26, 2017 05:12 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Error 3014 Cannot open any more tables
>
> I cleaned up some open recordsets in the code - I was *not* as clean in my
> code as I thought I was.  This alone did not correct the issue, though - 30
> minutes after launch it hit the 3014 error again.
>
> BUT:  I then ran the front end through  Dan Waters' Decorrupter tool, and
> it ran for over 3 hours without a hitch.  I closed it so that I could
> upload it to the network and deploy it;  she's running again and  I will
> let 'er run overnight.  We'll see if she's still going come morning.
>
> Janet
>
> On Wed, Apr 26, 2017 at 3:24 PM, John Colby <jwcolby at gmail.com> wrote:
>
> > It turns out that any form and any object like a list or combo on the
> form
> > also opens a recordset.  Thus this exact problem can be caused by overly
> > complex forms.  However she expressly says it is a single "simple" form
> so
> > that is probably not the issue.  File sharing locks have nothing to do
> with
> > the recordsets in a FE however.  Those locks control access to the BE.
> >
> >
> > On 4/25/2017 6:55 PM, Rocky Smolin wrote:
> >
> >> Is there a registry key that can be increased like the MaxLocksPerFile
> >> hack:
> >>
> >> https://support.microsoft.com/en-us/help/815281/-file-sharin
> >> g-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
> >>
> >>
> > --
> > John W. Colby
> >
> >
> > --
> > 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
>