When I design a system, I would like to design it so it can be
converted to SQL with as least work as possible.
I think Allen overrates the difference between a well-designed
Access front end for use with a Jet back end and for use with SQL
Server. The same things that make a client/server app efficient will
also enhance the efficiency of an app using a Jet back end. The main
principle is:
Don't retrieve data until the users needs it.
That means:
1. never bind a form to a full table -- ask the user which
record/records to load and then write the recordsource on the fly,
with a custom WHERE clauses limiting the number of records to
exactly those that are needed.
2. don't populate subforms that aren't visible to the user -- if
you've got a tabbed form with subforms on different forms, don't
load the subforms until the tab is displayed. Alternatively, don't
assign a recordsource to the subforms until the tab is displayed.
The tab control's OnChange event can handle this easily. This is one
where you will likely compromise, as heavyweight subforms may take a
long time to populate, so you may want to take the performance hit
for those particular subforms in the OnCurrent event of the main
form instead of in the OnChange event of the tab, simply because it
may be better for your users to have the wait to load the data in a
different place.
3. limit all listboxes and combo boxes to reasonable numbers of
records -- there is no such thing as a user-friendly combo box with
10,000 records. In those cases, load the recordsource on the fly
after the user has typed 2 or 3 characters. This will mean that
there is no hit to retrieve the combo box data until the user
actually tries to *use* the combo box. There are some issues with
this, such as displaying previously-entered data, but that can be
worked around in the OnCurrent event of the form the combo box is on
by assigning the recordsource on-the-fly to display just the record
with the current value the combo box is bound to. This can also
cause issues with continuous forms, but my opinion is that
continuous forms should not be used for editing, so combo boxes
don't belong on them in the first place.
That's just three things that follow from the main principle. These
things will enhance the efficiency of an application regardless of
what the back end is, and I've used these principles for years in my
Access/Jet apps on the theory that I might one day upsize to SQL
Server. The end result is that whenever I *have* ended up upsizing
(and it's been surprisingly seldom, precisely because the apps
worked so well as designed with a Jet back end), it's taken very
little work to make things efficient with SQL Server -- that work
was already part of the underlying design of the app from the very
beginning.
