1 link or query vs. multiple links or queries

[R. Wilson]

Access 2002. I have built a form for retrieving inventory part information.
The main form pulls in the basic static information of the part. Then I have
10 subforms, each pulling more dynamic info like order status, history,
demand, etc. It seems that each subform adds about 1-1.5 seconds to the
entire loading process. In all I am pushing 12-15 seconds for loading, which
I feel is beyond the comfort range of the end-user. Most of my data comes
from linked SQL server tables and a few tables embedded in the database.
Since the subform links to the main form it is not necessary to build a
unique query for each subform, but I am wondering now if maybe I should be
and that maybe the query handles the subforms one after the other in a
series, whereas if each subform had it's own query maybe they would run
together in parallel.

Likewise, I am wondering also if the linked tables might also serve better
if there was a unique link serving a each query.

Any enlightenment of my understanding in this area would be greatly
appreciated.

Thanks, Rob Wilson

 

[John Vinson]

Access 2002. I have built a form for retrieving inventory part information.
The main form pulls in the basic static information of the part. Then I have
10 subforms, each pulling more dynamic info like order status, history,
demand, etc. It seems that each subform adds about 1-1.5 seconds to the
entire loading process. In all I am pushing 12-15 seconds for loading, which
I feel is beyond the comfort range of the end-user. Most of my data comes
from linked SQL server tables and a few tables embedded in the database.
Since the subform links to the main form it is not necessary to build a
unique query for each subform, but I am wondering now if maybe I should be
and that maybe the query handles the subforms one after the other in a
series, whereas if each subform had it's own query maybe they would run
together in parallel.

If each Subform is displayed on a separate Tab Page on your form, one
idea would be to have each Subform's RecordSource blank to begin with.
In the Change event of the tab page you could set the Recordsource
property of the subform (or subforms) on that page to a SQL string
retrieving the desired child records.

I'm not following what you mean, though, with the single query. Are
ALL of these subforms referencing the same recordsource? If so then
yes, this will REALLY beat up on performance. Each subform should be
based on a query which retrieves only those fields needed specifically
on that subform.

Likewise, I am wondering also if the linked tables might also serve better
if there was a unique link serving a each query.

I'm sorry, I can't parse that sentence; could you reexplain it?

John W. Vinson[MVP]