[AccessD] Idle Access seems to drop connection

Wed Nov 1 15:24:18 CDT 2017

If you have that many rows, you need to consider moving the BE to SQL
Server Community Edition or MySQL or some other genuine BE.

Arthur

On Wed, Nov 1, 2017 at 2:19 AM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> 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
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>

-- 
Arthur