[AccessD] Access 2007 FE/BE speed

jm.hwsn jm.hwsn at gmail.com
Thu Feb 10 10:32:21 CST 2011


That sounds more elegant than the code I found.
Would you mind sharing the code?
Thanks,
Jim 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
Sent: Thursday, February 10, 2011 10:14 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access 2007 FE/BE speed

Persistent connection means that you open one recordset to each BE and *hold
it open*.  What this does for you is gets the lock used to open the BE and
holds it open.  It is that first "connect to the BE file" lock that takes
the longest and can be quite time consuming (5-10 seconds) if there are
dozens of users in the BE.

I have written code which opens a recordset and stores a pointer to the open
recordset in a list.  I actually use about a half dozen BEs at one client so
I have a half dozen recordsets open and held open.

I went so far as to create a tblHoldOpen with zero records in it, one
tblHoldOpen in each BE.  I then open a recordset SELECT * FROM
tblHoldOpenXYZ.  These are LINKED to tblHoldopen in BE XYZ.  So i open each
tblHoldOpen in each BE and then throw a pointer to that recordset into a
collection where it stays open until the FE closes.

Voila, persistent connections.

John W. Colby
www.ColbyConsulting.com

On 2/10/2011 10:01 AM, jm.hwsn wrote:
> Good points Jim, thanks.
> I have turned off the sub-datasheets... I usually do that regardless 
> of the version.
> I don't have any control over the servers, but I can ask them to make 
> some changes.
> I never thought about the virus scan... I'll check into that.
> Thanks,
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
> Sent: Thursday, February 10, 2011 8:52 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Access 2007 FE/BE speed
>
> Jim,
>
>   The exclusive point is in regards to the FE in a split design, which 
> each user should have their own copy of, so opening exclusive would be 
> appropriate.
>
> Some other items:
>
> 1. Make sure the new ACE DB's are not being virus scanned.  Since the 
> extension has changed, anti-virus sometimes kicks in.
>
> 2. Turn off the sub datasheets feature on all tables (FE and BE).  Not 
> specific to A2007, but often overlooked.
>
> 3. Turning off OPLOCKs on the server, but that impacts all apps and 
> again, this applies to all versions (not A2007 specifically).
>
> 4. Often, A2007 is used in conjunction with a new server like 2008.  
> It appears that the SMB 2.0 specification is giving some apps 
> headaches, although this is not fully clear yet.  Some report the app 
> works better when the server is forced to use SMB 1.0 protocol, others 
> 2.0, so your mileage may vary.
>
> Jim.
>
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn
> Sent: Thursday, February 10, 2011 08:58 AM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Access 2007 FE/BE speed
>
> Access 2007 is inherently slow on a network configured using FE/BE
scenario.
>
> I have scoured several websites and spent a lot of hours, to see if 
> A2007 can be modified to speed it up.
>
> Several "tips" were obscure on the sites and might only be mentioned 
> in passing.
>
> Here is what I've found:
>
> 1)  ensure there is a persistent connection to the BE.  Code can be 
> found on the FMS, Inc. site.
>
> 2) Ensure the BE location is in a "Trusted Location" in the FE.
>
> 3) Change record-level locking.  I set the following:
>
>        OLE/DDE timeout: 30
>
>        Refresh interval: 30
>
>        Number of update retries: 2
>
>        ODBC refresh interval: 120
>
>        Update retry interval: 250
>
> 4) Turn off unused user interface features such as: Show animations 
> and Show Smart Tags on Datasheets
>
>
>
> In my somewhat limited test, my FE runs almost as fast as an 
> integrated file. I know it's running little slower, but I can barely 
> see the difference
> - I can live with it.  The biggest improvement came when I did 2 and 3 
> above.  I still haven't done or tested number 1 above.
>
>
>
> Other items that are recommended are:
>
>       change the default open mode:  Exclusive
>
>       default record locking:  Edited Record.
>
> However, I don't think these would work with a shared BE.  I don't 
> think there is a way to make these different for two separate files.
>
>
>
> Do you have any other ideas or words of wisdom or comments?
>
> Thanks,
>
> Jim
>
>
>
>
>
> --
> 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




More information about the AccessD mailing list