Loading Speed of Form - Please Help

[nxqviet]

Hi all, I have a question regarding the loading speed of forms.

I have about 6 forms in my database, each is loaded with lot of
controls and VBA driven action that calls numerous queries. So as
expected, they do take a while to load, 10 - 30 seconds. For some
remote desktop users, it usually take even longer. These forms,
however, do not have any onLoad. procedures. All the code are in click
of buttons of afterUpdate of dropdown menu and others.

The interesting thing is, these form load much much faster (3 secs)
when one form opens the next, instead of manually open it from the
database window. Is there any explanation for this? And is there any
way to reduce the load time in general? Would it help to clear memory
after every action (how do I do that too)? But since there are many
users accessing this db at the same time and via a central terminal
server, it might not be a good idea to clear the server's memory (I'm
I right?).

I desperately need to reduce this annoying loading time. Please help.

Any comment or suggestions will be greatly valued. Thanks All.

 

[aaron.kempf]

I would reccomend

a) write tighter WHERE clauses, and less columns if possible
b) using Access Data Projects
c) 'index tuning wizard' or 'database tuning advisor'

MDB doesn't have the capability to run profiler; so I believe that MDB
is a complete and utter waste of time

-Aaron

 

[Tony Toews [MVP]]

I have about 6 forms in my database, each is loaded with lot of
controls and VBA driven action that calls numerous queries. So as
expected, they do take a while to load, 10 - 30 seconds. For some
remote desktop users, it usually take even longer. These forms,
however, do not have any onLoad. procedures. All the code are in click
of buttons of afterUpdate of dropdown menu and others.

The interesting thing is, these form load much much faster (3 secs)
when one form opens the next, instead of manually open it from the
database window. Is there any explanation for this?

Yes, this is the problem where a second user it experience severe performance
problems. A persistent connection of some sort solves this problem. An example
would be keeping one form always open at all times.

And is there any
way to reduce the load time in general?

The three most common performance problems in Access 2000 or newer are:
- LDB locking which a persistent recordset connection or an always open bound form
corrects (multiple users)
- sub datasheet Name property set to [Auto] should be [None]
- Track name AutoCorrect should be off

If the problem is for everyone when starting up the MDB then it likely needs a
decompile.

For more information on these, less likely causes, other tips and links to MS KB
articles visit my Access Performance FAQ page at
http://www.granite.ab.ca/access/performancefaq.htm

Would it help to clear memory
after every action (how do I do that too)? But since there are many
users accessing this db at the same time and via a central terminal
server, it might not be a good idea to clear the server's memory (I'm
I right?).

No idea what you mean by this. And I suspect you might not know either. <smile>

You have split the MDB in a front end and back end are giving each user their own
copy of the FE MDB/MDE, right? If you have then ignore my standard blurb which
follows.

You want to split the MDB into a front end containing the queries, forms, reports,
macros and modules with just the tables and relationships. The FE is copied to each
network users computer. The FE MDB is linked to the tables in the back end MDB
which resides on a server. You make updates to the FE MDB and distribute them to
the users, likely as an MDE.

See the "Splitting your app into a front end and back end Tips" page at
http://www.granite.ab.ca/access/splitapp/ for more info. See the Auto FE Updater
downloads page http://www.granite.ab.ca/access/autofe.htm to make this relatively
painless.. The utility also supports Terminal Server/Citrix quite nicely.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm