[AccessD] Access 2007 FE/BE speed

jwcolby jwcolby at colbyconsulting.com
Fri Feb 11 12:58:49 CST 2011


It sounded as if you created a special FE to do something.  On reread I understand that I just 
misread your post.

John W. Colby
www.ColbyConsulting.com

On 2/10/2011 2:23 PM, Heenan, Lambert wrote:
> Ya got me there John.
>
> What do you mean by "for the FE that is the application"?
>
> I'm talking about the usual split: a front end with forms and queries and all that other good stuff, along with a back end that has the data.
>
> In actual fact the front end is using more than one back end, but there is only one back end in the set that actually gets updated. The others are there for read only purposes. So I open a persistent connection to the back end that gets data written to it and just use the others when needed.
>
> So again, what did you mean by "for the FE that is the application"?
>
> Lambert
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Thursday, February 10, 2011 2:00 PM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] Access 2007 FE/BE speed
>
> But that opens a persistent connection *for that fe* but not for the FE that is the application.
>
> John W. Colby
> www.ColbyConsulting.com
>
> On 2/10/2011 12:25 PM, Heenan, Lambert wrote:
>> I just open a recordset on a table in the back end (as it happens a dedicated dummy table that is not used for anything else) in a Front End application form that opens hidden when the application starts up.
>>
>> That form's main purpose in life is to check if any user is not doing anything for n minutes, and if so it automatically shuts the database down.  In the Close event of the form the recordset object is closed.
>>
>> Lambert
>>
>> -----Original Message-----
>> From: accessd-bounces at databaseadvisors.com
>> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jm.hwsn
>> Sent: Thursday, February 10, 2011 11:32 AM
>> To: 'Access Developers discussion and problem solving'
>> Subject: Re: [AccessD] Access 2007 FE/BE speed
>>
>> 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
>>
>> --
>> 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