Access speed very slow on forms

[Tim]

Good morning,

I have a few forms that link multiple subforms and I am finding that
the access time on these forms is really slow (typically takes about
twenty seconds to load it up on my computer).

I have a split database (front end and back end) with the back end
residing on the server and the front end being local. Now, my
understanding of the JET DB engine is that entire tables are loaded
into the local machine's memory as a replica and then the table is
synchronized with the server once a field has been changed (please let
me know if my thinking is incorrect). If this is the case, each
subform's underlying tables must be loaded as well (and each subform's
datasource is a query which typically touches on more than one table).
If this is the case, would it make sense to link the subforms "on the
fly" through VBA, since the subforms reside on a tab control and are
not seen by the user unless the correct tab page is focused? I feel
like I am missing some important tidbit of information, because my
database isn't huge by any stretch (it has about 55 tables, 40 queries
and maybe a total of 40000 records across those tables) yet the speed
is so crappy.

Any tips on making my access speed faster would be greatly
appreciated.

Tim

 

[Frits van Leeuwen]

Good morning,

I have a few forms that link multiple subforms and I am finding that
the access time on these forms is really slow (typically takes about
twenty seconds to load it up on my computer).

I have a split database (front end and back end) with the back end
residing on the server and the front end being local. Now, my
understanding of the JET DB engine is that entire tables are loaded
into the local machine's memory as a replica and then the table is
synchronized with the server once a field has been changed (please let
me know if my thinking is incorrect). If this is the case, each
subform's underlying tables must be loaded as well (and each subform's
datasource is a query which typically touches on more than one table).
If this is the case, would it make sense to link the subforms "on the
fly" through VBA, since the subforms reside on a tab control and are
not seen by the user unless the correct tab page is focused? I feel
like I am missing some important tidbit of information, because my
database isn't huge by any stretch (it has about 55 tables, 40 queries
and maybe a total of 40000 records across those tables) yet the speed
is so crappy.

Any tips on making my access speed faster would be greatly
appreciated.

Hello Tim,

Ok my English is not so very well, but I give it a try.
If I understand well, You have 1 form with many subforms in it. Are they al
togther on screen? If so, then I had the same problem. Then it's going to be
a slow system.
I choose: 1 form with a changing subform. I made tabs by my self and changed
the subform when I cklick at a tab. I't works very wel.

How do you use your indexes? Make a index where it's nessesary, that's
quicker!

Frits

 

[Tim]

Hello Tim,

Ok my English is not so very well, but I give it a try.
If I understand well, You have 1 form with many subforms in it. Are they al
togther on screen? If so, then I had the same problem. Then it's going to be
a slow system.
I choose: 1 form with a changing subform. I made tabs by my self and changed
the subform when I cklick at a tab. I't works very wel.

How do you use your indexes? Make a index where it's nessesary, that's
quicker!

Frits

Thank you for the help Frits. What you describe is what I found in an
article here: http://www.databasejournal.com/features/msaccess/article.php/3599781.
I have implemented these dynamic forms now and the performance has
definitely increased. There is still a bit of a lag when the subform
is being dynamically linked, but I think it is a network issue more
than an Access issue, so what I've got seems to be okay for the time
being.

Tim

 

[Albert D. Kallal]

Now, my

understanding of the JET DB engine is that entire tables are loaded
into the local machine's memory as a replica and then the table is
synchronized with the server once a field has been changed (please let
me know if my thinking is incorrect).

100% wrong.

JET only loads the records you send to the form. Now, of course, if you open
form to a large table, then ms-access does it best not to load very many
records. However, if you ask the user BEFORE you load the form and use the
where clause to load one record, then only one record retrieved (as long as
a index can be used).

So, no... the whole table is not send down the wire.

the best approach is to simply limit the records you send to a form. I give
an example idea of a search form here that accomplishes this goal:

http://www.members.shaw.ca/AlbertKallal/Search/index.html

As for performance, check out the following list:

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

#1 on your list should be a persistent connection.

 

[Guest]

No, and see Tony's Performance FAQ as mentioned in this
thread.

But your conclusion is correct, as far as it goes. Subforms are
loaded before the main form, and need to be synchronised with
the main form, sometimes twice, before the form is displayed.
So it makes sense to load them dynamically, so that they load
the correct information first time, and don't need synchronisation.

(david)