jm.hwsn
jm.hwsn at gmail.com
Thu Feb 10 12:21:21 CST 2011
Essentially, I'm doing this, I just didn't realize I was keeping the connection open. When the FE opens, the user fills in their credentials in the log in form. Once verified, the user's Personal ID populates a couple of fields in the bound log in form. When the next button is clicked the log in form is hidden throughout the entire session. The fields are used for various processes during the session. The fields are tied to all the forms and results that the database represents to the user. Thanks, Jim -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Heenan, Lambert Sent: Thursday, February 10, 2011 11:25 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] Access 2007 FE/BE speed 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