[AccessD] Idle Access seems to drop connection

[David Emerson]

Thanks to all that responded.  We tried all the tricks and nothing made any
difference.  In the end we deleted a whole heap of old records reducing the
number from 600,000 to about 200,000.  This reduced the delay within
acceptable range.

Regards

David Emerson
Dalyn Software Ltd
Wellington, New Zealand



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Dettman
Sent: Saturday, 21 October 2017 12:16 a.m.
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Idle Access seems to drop connection

David,

 It would depend on the setup.  Other databases may be using other mapped
drives, staying active, etc.

 Here's what you need to do to see if it is a mapped drive disconnect:

1. Ensure that all users are out of the DB 2. Verify that by checking the BE
directory for an .LDB file.  There should be none.
3. Open the app on the station and make sure you leave a form open.  As long
as a form is open, then you will have a login on the BE database and a LDB
file.
4. Leave it sit ten minutes.
5. On the PC, open windows explorer and see if the mapped drive shows a red
X.  Do not click on the drive.
6. Do something with the form and see if you get the delay (you should).
7. Now look at explorer...the red X should be gone.
8. Go back to the form...you now should see no delay.

There is one other issue that can be at play here as well.  That is OPLOCKS,
or opportunistic locking (aka client side caching).  This is built into
windows and can lead to delays of up to 30 seconds.    Leaving a connection
open for the life of the app will avoid this for the most part.

 What happens is when a single user is in a file, Windows places an OPLOCK
that says the user has exclusive use of the file and can cache changes on
the windows client.    When another user comes along and wants to use the
file, the server must break the OPLOCK.   That process can take up to 30
seconds.   If your app is constantly closing/opening the BE, then you can
encounter this a lot.   But if this is the only user seeing the delay, then
it is most likely something else.  Still though a possibility depending on
the pattern of usage from the users.

 Doesn't hurt though by any means to keep a constant connection open in the
app and in fact this is something every app should do.  Closing/opening a BE
constantly carries a lot of overhead.

Jim.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
David Emerson
Sent: Thursday, October 19, 2017 07:39 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Idle Access seems to drop connection

If that was the case, wouldn't we see the same behaviour in the other
database that doesn't have the problem? 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim
Dettman
Sent: Friday, 20 October 2017 11:09 a.m.
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Idle Access seems to drop connection

Windows drops inactive mapped network drives....default is 10 minutes.

You can disable that with a GPO or by modifying the registry.

Other potential item; do you keep a connection open to the backend for The
life of the app? Could be the connection to the backend database is closing
and when logging back in again there s quite a bit of overhead.  30 seconds
is excessive though.

More than likely it s a map drive disconnecting

Jim

Sent from my iPhone

> On Oct 19, 2017, at 5:56 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:
> 
> Hi Arthur,
> The user can have the orders screen open and just not doing anything 
> with it for 5 minutes.  When he then tries to run a report from that 
> screen, or close the screen and reopen it (i.e. query the database
> again) there is the delay.  In this case isn't the orders screen 
> effectively doing what you suggest?
> Regards
> David
> 
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Arthur Fuller
> Sent: Friday, 20 October 2017 10:24 a.m.
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Idle Access seems to drop connection
> 
> There have been threads on this. If you declare a public variable and 
> open a recordset witb it you won't lose your connection.
> 
> On Oct 19, 2017 4:47 PM, "David Emerson" <newsgrps at dalyn.co.nz> wrote:
> 
> Hi Team,
> I have a client that has several users with FE's connected to the same BE.
> All of them seem to work fine except for 1 user.
> When this user starts his copy there is a 30 second delay while the 
> programme opens (it gets data to display a report).  Then when the 
> orders screen is open via a command button it opens relatively quickly 
> (within a couple of seconds).
> However, if he leaves the system for 5 minutes or so then the next 
> time he does something that requires getting data (for example closing 
> and opening the orders screen) there is a 30 delay again.  After the 
> initial delay the system works fast again.  If he immediately closes 
> and opens the orders screen without a delay between tasks then it 
> opens immediately without a delay.
> I know there is a timeout setting for SQL connections but this is 
> purely an Access BE.
> The IT company has checked the network (it is a wired network) and all 
> seems to be fine.  The user has another similar database (although 
> smaller in data
> size) and it doesn't have the same problem.
> Any thoughts on what may be causing the delay?
> Regards
> David Emerson
> Dalyn Software Ltd
> Wellington, New Zealand
> 
> 
> --
> 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

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