CODEWITHSUNDEEP
ms-accessvba
Icode4food
Icode4food

Reputation: 8694

me.visible = false instead of closing form

In my MS Access application I have several forms that are very data intensive (several subforms based on even more tables). My users are complaining that when opening the data across the network the load times are unbearably long.

I have do have a slit front end / back end setup using the excellent autofe application.

One solution I have come up with to the problem is instead of docmd.close when the user clicks the "Save & Close" button I me.visible = false. The user then has the long wait time the first time after the application is loaded but for later loads performance is improved by a noticeable amount.

So far this has been working fairly well. I am just concerned that there may be some hidden gotchas hidden in this strategy that I haven't encountered yet.

My users aren't overly intelligent and I don't use the application myself so I can't expect to get meaningful feedback if something is behaving erratically.

Anyone else employed this strategy successfully or know of a good reason not to do it?

Upvotes: 3

Views: 4557

Answers (4)

iDevlop
iDevlop

Reputation: 25252

At first, just hiding the form is not too bad, I think.
I would dig a bit more on WHY your load times are so long. You mentionned several subforms. Are they all displayed at the same time, or are they in the various pages of a Tab control ?
In the latter case, you could quite easily unbind the subforms that are not visible, and bind them on the PageClick event. That makes a big difference in performance.
EDIT:
Also, a bit out of scope for this question, but good for every performance issue:
-did you double check that the foreign keys in the related tables are properly indexed ?
-make sure the back-end is regularly compacted.

Upvotes: 4

Tony Toews
Tony Toews

Reputation: 7882

Yes, I've doen the same thing myself in very complex forms which had about 10 or 15 tabs each with a subform. Worked for at least ten years. You had to watch for varous form level values or unbound controls which you assume start as null or zero. But once it's running smoothly it should run just fine. We had to this back in Access 97 days because Access would crash with out of memory errors after the users had opened and closed varous forms thousands of times per day.

Upvotes: 1

HansUp
HansUp

Reputation: 97101

Anyone else employed this strategy successfully or know of a good reason not to do it?

Yes, that strategy is similar to recipe #8.1 Accelerate the Load Time of Forms from the second edition of the Access Cookbook. However that recipe pre-loads a set of forms, with WindowMode:=acHidden, at database startup. So the tradeoff is that database startup takes longer, but subsequent form opens (for the pre-loaded forms) are comparatively fast.

The discussion for that recipe didn't mention any drawbacks for that technique. In limited use, I haven't discovered any. And since it seems to improve your users' experience, I would continue to use it.

Beyond that, I would take a hard look at the amount of data your forms pull from the back-end database. Limit the number of rows retrieved as the Record Sources for the main and subforms. Give the user a method to select a different record or small set of records. Also make sure you use indexing to support Record Source WHERE and ORDER BY clauses. Avoid WHERE conditions that use functions which will force a full table scan to figure out which rows to exclude from the Record Source. Similar considerations apply to combo and list boxes which use saved queries or SELECT statements as their Record Sources; if you can't limit the rows, at least make sure to optimize data retrieval.

Upvotes: 4

Lance Roberts
Lance Roberts

Reputation: 22842

Are you making sure that the data gets refreshed in an appropriate timeframe?

Upvotes: 1

Related Questions