Tab Control - best loading method

[Guest]

Access 03,

I have a form that loads a fair amount of data. Much of the data is spread
across roughly 10 tab control pages. Most of these pages are comprised soley
of subforms. I have the tab style set to none and use command buttons to
select the appropriate page.

My question is: Is it better for me to load everything in the form at once
when opening the form, or should I load the data to the pages and subforms as
the user selects the corresponding command button to load the page?

TIA

Aaron G
Philadelphia, PA

 

[Allen Browne]

If you do not actually need the data on any more than one page at a time,
one option is to place a subform directly on the main form (not on the page
of a tab control), and change its SourceObject property so it loads the
subform you want to see.

 

[Guest]

Allen,

I actually thought about that, but I wasn't sure if that would be faster or
if there would be other issues I don't know about. Is there any "loading
speed hiarchy" that I could follow - something that tells me what's fastest,
second fastest, third etc?

Thanks for your time.

Aaron G
Philadelphia, PA

 

[Allen Browne]

It's a bit too involved for that, Aaron.

There's an overhead for each object and for each set of data. For example, a
text box takes slightly more resources than a label does because it has a
Control Source value. A list box or combo takes considerably more because it
also has a RowSource to load. A subform takes more than that again, because
it has many more properties to manage, has its own updatable RecordSource,
and may contain other objects that have further RowSources etc.

Each RecordSource/RowSource might be a simple table with a primary key and
no criteria or sorting (fast), or it might be a slow query that sorts,
groups, and uses criteria that cannot use indexes and loads buckets of
records. Access may be able to optimise through clever optimization
techniques (such as applying the LinkMasterFields/LinkChildFields and/or
filter before actually loading all records) or that may not be possible. Or
there might be things that will slow it down (such as inefficient ODBC
connection to unindexed data sources, or calls to domain aggregate functions
or inefficient user-defined functions that must execute on every row of the
query.)

Then there is the overhead associated with loading the VBA into memory
(potentially compiling the code and disambiguating all the library
references) determining the optimal compilation for each query statement,
handling the calculated controls and interactions between them, handling any
conditional formatting, handling each VBA event, and so on.

After that, there are factors such as the speed and fragmentation of the
drive, available memory and page file (remember every object requires RAM to
handle it), and what other processes are running.

In short, anything you can do to minimize memory usage is helpful.
Particularly useful techniques include:
- loading only one subform instead of 10 (as suggested),
- using indexes astutely,
- avoiding domain aggregate or user-defined functions that open a recordset
in every row of a query,
- delay-loading combos that have huge numbers of rows:
http://allenbrowne.com/ser-32.html