A
AG
Hello,
I was hoping to get some expert advice, on deciding whether a query or a
filter is better in general, when populating a form, in a multiuser
environment. I usually have my users query via a form by whatever
parameters are approrpriate, which then opens another form and populates
it with the resutl(s). If the query returns more than a certain amount
of records, the application instead returns the results to a continuous
form that shows only the most relevant aspects of the record (usually 5
or 6 fields), and by clicking on the record, it opens a form which
returns/shows that whole record. I don't remember, exactly, but I think
I picked this up from the Access97 book by John Viescas, and his
entertainment database example, and this sentiment has somewhat been
addressed by other experts such as Larry Linson (quoted in part, below,
from a reply posting he made in 2004).
I was recently told by another developer, that using a filter or query
makes no real difference on performance or risk of possible corruption
(due to extra network traffic, stress, errors, etc.), and that I was
wasting my time by doing the above queries and forms. Instead, opening a
form which is linked to a 10,000 record table and then going into a
"filter by form" mode, to allow the user to filter the records is neither
better nor worse than my "restricted" way of doing things.
Can anyone comment on whether a query is preferred over a filter - not to
say who is right or wrong - but rather which way may be more "efficient",
when using access in a multiuser environment? I'd really like to know if
I have been wasting my time, over the years.
Thanks
Al
Larry Linson on populating a report via query vs. filter:
I can't give you a definitive answer on this, but I have always thought
that
was the case -- that the RecordSource determined what was retrieved and
the
Filter determined which of those were displayed. I have observed enough
delay to be convinced on a split database on an older, slower network
when
retrieving whole tables on Forms which could later be Filtered that it
was
returning _more_ than a Query or SQL string that limited the records.
I was hoping to get some expert advice, on deciding whether a query or a
filter is better in general, when populating a form, in a multiuser
environment. I usually have my users query via a form by whatever
parameters are approrpriate, which then opens another form and populates
it with the resutl(s). If the query returns more than a certain amount
of records, the application instead returns the results to a continuous
form that shows only the most relevant aspects of the record (usually 5
or 6 fields), and by clicking on the record, it opens a form which
returns/shows that whole record. I don't remember, exactly, but I think
I picked this up from the Access97 book by John Viescas, and his
entertainment database example, and this sentiment has somewhat been
addressed by other experts such as Larry Linson (quoted in part, below,
from a reply posting he made in 2004).
I was recently told by another developer, that using a filter or query
makes no real difference on performance or risk of possible corruption
(due to extra network traffic, stress, errors, etc.), and that I was
wasting my time by doing the above queries and forms. Instead, opening a
form which is linked to a 10,000 record table and then going into a
"filter by form" mode, to allow the user to filter the records is neither
better nor worse than my "restricted" way of doing things.
Can anyone comment on whether a query is preferred over a filter - not to
say who is right or wrong - but rather which way may be more "efficient",
when using access in a multiuser environment? I'd really like to know if
I have been wasting my time, over the years.
Thanks
Al
Larry Linson on populating a report via query vs. filter:
I can't give you a definitive answer on this, but I have always thought
that
was the case -- that the RecordSource determined what was retrieved and
the
Filter determined which of those were displayed. I have observed enough
delay to be convinced on a split database on an older, slower network
when
retrieving whole tables on Forms which could later be Filtered that it
was
returning _more_ than a Query or SQL string that limited the records.