[AccessD] Error 3014 Cannot open any more tables

[Jim Dettman]

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