Best practice - queries, lookup, use table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello -

I have seen a surprising amount of different ways to handle relationships
between tables and forms coding, particularly from Microsoft, with no mention
of the best thing to use and why.

It seems to me the best thing to do would be use queries, because they're
pre-compiled. Microsoft consistently provides samples that put the query
text for a combobox, i.e., under the row source, as opposed to a query.

Any thoughts on the above? I am developing a database for 10 concurrent
users and want it to be as speedy as possible.
 
Sandy, Microsoft does point out that saved queries are precompiled. In fact,
precompilation makes no practical difference at all at current processor
speeds. It occurs only at the beginning of the operation (not for every
record), so it is generally irrelevant.

(If you want to make a point of it, you could also argue that a query that
was compiled when there was very little data may actually have a poor plan
saved, whereas a non-compiled query will choose a plan that is relevant for
the current data.)

In practice there are many other factors that make a much larger difference.
Where people include a reference to a text box on a form in a saved query,
they then regularly ask how they can handle the situation when the user
leaves the text box blank, and they are adding additinal criteira to cope
with that. The criteria has to be evaluated for every record in the query,
and so the query runs *much* more slowly than a dynamically generated query
that only contains the criteria that are actually needed.

So, when dealing with data in Access (JET) tables, dynamically generating a
SQL statement (or a WHERE clause) will generally be much more efficient than
the saved query with form references.

Just as importantly, dynamically generating the SQL statements you need in
code makes the database much easier to maintain. The code is not dependent
on a separate query that somebody might change and therefore mess up your
code. You can copy the module to somewhere else without having to worry
about which queries are needed. And there are far fewer saved queries to
manage in the database.

Just my opinionated view.
 
Thanks for your reply, Allen.

Perhaps I can just press you for another answer - I have seen a lot of bad
press about lookup columns in tables in Access. Is this "bad press"
warranted? Would you recommend their use?
 
Sandy, the article Doug pointed you to is a good summary of the issues.

Just to clarify, what we don't like is:
a) the use of combos in tables, and
b) some issues caused by the Lookup wizard in the table.

The idea of foreign key fields is not the problem. That is, every serious
database will have lots of fields that relate to other lookup tables, such
as categories. There is no problem at all with using such fields: they are
essential.
 
Back
Top