[AccessD] Error 3014 Cannot open any more tables

[Jim Lawrence]

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

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com