Increase Form Opening Speed

[cneill]

I have an Access 2002 Multi user environment, with the Database split Front End/back end. The Back end is on the Company Server.
I placed only the essential tables on the Server, to minimise net work traffic.
I know it depends on Broadband speed but are there ways to improve the speed the form opens. Currently it can take up to 45secs to open, which is not good for the user.The Form has a subform both running from queries these forms are linked with two child fields and two master fields.
Does anyone have any ideas that might help me?

 

[MajP]

Not sure everything that you have tried, but here may be a few strategies that you have not.

http://www.fmsinc.com/tpapers/faster/index.html#Forms

 

[deepgrewal22]

Try to minimize the amount of code that exists in the FORM LOAD event. Perhaps you can use a Timer or another event to execute some of the code that is in your FORM LOAD event.

Also, I would probably check to see if the queries that are being run are absolutely needed at the time they are running. Meaning that, could these queries be placed in another event or be initiated by the user?

Deep Grewal
"Microsoft Works" - oxymoron

 

[randy700]

Is the front end on the user's machine instead of on the network server?

Randy

 

[cneill]

Hi Randy700
The front End is on the user Machine not on the Network Server.

MajP
thanks for the link, have done some of them but not all,so working on them now, one interesting one is
"You can significantly increase the performance of opening tables, forms, and reports that are based on linked tables by having Microsoft Access keep the linked table's database open. To do this, open a Database variable in Visual Basic code using the OpenDatabase method. Keep this variable open as long as your application is running. This forces Access to keep that database open, making access to linked tables much faster."
Can anyone advise me how I can do this?

 

[MajP]

I do not have the original link:

When the symptoms encountered indicate that performance is acceptable with a single user in the database but drops significantly when two or more users are in the database, the problem may be caused by interaction with the LDB file.

In Access 2000, when a second and subsequent user tries to access a shared backend database on the server, there seems to be a situation where Access tries to perform a delete on the LDB file (which fails because another user is currently in the file). This attempt is made about 15 times before silently failing and the records are returned from the linked table.

To resolve this issue we need a persistent connection to the back-end from each of the front-end workstations. This can be done using a bound form which is always open or by keeping a recordset open at all times..

A good way of testing this is to ensure you are the only one opening the back end. Then run the front end database until it gets to the main menu. At this point you should see an LDB file present on the server with the same name as the back end.

If you don't see this LDB file then you know you don't have a persistent connection.
Bound form

The simple situation would be to create a simple form based on a table. Bound form means that the record source of the form ha a table or query specified. Any table but one with as few records as possible. Or create a dummy table and put one record in it. In your startup form add the following line of code in the startup forms OnOpen event.

DoCmd.OpenForm "frmKeepOpen", acNormal, , , , acHidden

I always have a global options table containing such details as default corporate logo, report banding true/false and colour, bar code font file name and so forth. I have a hidden form bound to this table which is always open. I then reference these fields as appropriate in code using forms!GlobalOptions!goReportBandingColour and forms!GlobalOptions!goCorporateLogoPathandFilename.
Global Recordset which is always open

Alternatively you can create a dummy (test) table in the backend file and create code in the front-end file which opens a recordset on this table and persist the recordset until the front-end app is closed. To do so:

Create an empty form. .

Declare a recordset variable in the global declarations section.

In the OnOpen event open a recordset against any table.

In the OnClose event, which will fire when the MDB is closed, close the recordset and Set variable to nothing

Ensure you always open this form when opening the MDB. You will likely want to open this form hidden.

Maintaining persistent connections to linked tables could improve performance significantly because it prevents Microsoft Jet from constantly deleting, creating, and obtaining locking information from the other database's locking information file.

Public rsAlwaysOpen As Recordset

Private Sub Form_Close()
rsAlwaysOpen.Close
Set rsAlwaysOpen = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Set rsAlwaysOpen = CurrentDb.OpenRecordset("DummyTable")
End Sub
Global database connection which is always open

David Fenton suggests using a global database connection which is always open. Same concept as using a table in the above section.

Dim dbsAlwaysOpen As DAO.Database

Private Sub Form_Close()
Set dbsAlwaysOpen = Nothing
End Sub

Private Sub Form_Open(Cancel As Integer)
Set dbsAlwaysOpen = OpenDatabase("Q:\1 access\test 2000 BE.mdb", False)
End Sub

The problem with this approach is that you need to know the path and name of the backend MDB. While you can use a parsed linked table connection property this becomes more work.

 

[MajP]

Here it is the source

http://www.granite.ab.ca/Access/performancefaq.htm

 

[cneill]

Thanks very much got a bit of work to do now then.