Performance issue with subform

[Robert Azad]

Greetings,

I have a network based Access DB with front-end client and back-end server.
I have a performance problem with a form called Licence containing a subform.
The form uses the data contained in 3 tables based on an m:n relationship.
table 1: Licences, table 2 Lic-Contact, table3: Contacts
The Licence form displays licence data and includes a subform which displays
the contact info for licencee.

The form works OK as long as I don't use the feature Filter by Form. It
takes 3 seconds roughly to open and close the main form.
Considering we're on a busy network of about 4-5 hops between client and
server and a contact table
containing a few thousand records I suppose this is reasonable.

On the other hand if I use the Filter by form feature during the form's
lifetime, closing the form takes about 20 seconds
instead of the usual 3 seconds. As well, opening the form afterwards also
takes about 20 seconds.
I resolved the delay in closing by invoking a macro that closes the subform
before I close the main form.
That took care of the closing delay but I still have the delay of 20 seconds
on opening that I cannot get rid of!
The problem does go away if I remove the subform Licensees from the main
form.

Any suggestions would be much appreciated.

 

[Arvin Meyer [MVP]]

Properly indexed tables of less than 50,000 records should open a form in
well under 1 second. Check your indexing and make sure the fields are
properly indexed.

One of the poor ideas (IMO) of Access development is the habit of saving the
filter with the form after use. Go into design view and delete the filter
and save the form.