[AccessD] Error 3014 Cannot open any more tables

[John Colby]

No, not at all.  Sometimes ya gotta do what ya gotta do, and figure out 
a better way later.

On 4/27/2017 7:24 PM, Jim Lawrence wrote:
> Is that sarcasm?
>
> Jim
>
> ----- Original Message -----
> From: "John Colby" <jwcolby at gmail.com>
> To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
> Sent: Thursday, April 27, 2017 1:03:00 PM
> Subject: Re: [AccessD] Error 3014 Cannot open any more tables
>
> This is a great idea.  Open, run, shut down, rinse, repeat.
>
> On 4/27/2017 6:24 AM, Jim Dettman 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
>>>

-- 
John W. Colby