filter vs. query on a lan

  • Thread starter Thread starter AG
  • Start date Start date
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.
 
Al

What is it about Larry's advise (you attached it to the bottom of your post)
that you disagree with? It sounded like he said that returning ONE record,
via a query, was preferable to returning ALL records first, then filtering
the list down to a single record.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Al

What is it about Larry's advise (you attached it to the bottom of your
post) that you disagree with? It sounded like he said that returning
ONE record, via a query, was preferable to returning ALL records
first, then filtering the list down to a single record.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...


Huh? I don't see where I say I disagree, with Larry?

I said a developer (not Larry, of course) told me I was wasting my time
for prefering to have users query rather than filter the data, and the
extra work associated with that preference.

Because Larry is not difinitive (and clearly says so), I was hoping some
of the regulars could offer some of their thoughts, or experiences, etc.
on querying vs. filtering.
I can't give you a definitive answer on this, but I have always thought
that
was the case - [excerpt from quote by Larry Linson]
Click to expand...

Sorry if I wasn't clear on what kind of info I was asking for.
 
Al

You may have closed your excerpt too soon ... Larry went on to say "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."

That matches my experience, as mentioned in my previous post, that returning
a single record, via query or SQL will be much faster than returning all
records, then filtering down to a single record.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/


Ag said:
Al

What is it about Larry's advise (you attached it to the bottom of your
post) that you disagree with? It sounded like he said that returning
ONE record, via a query, was preferable to returning ALL records
first, then filtering the list down to a single record.

Regards

Jeff Boyce
Microsoft Office/Access MVP
Click to expand...


Huh? I don't see where I say I disagree, with Larry?

I said a developer (not Larry, of course) told me I was wasting my time
for prefering to have users query rather than filter the data, and the
extra work associated with that preference.

Because Larry is not difinitive (and clearly says so), I was hoping some
of the regulars could offer some of their thoughts, or experiences, etc.
on querying vs. filtering.
I can't give you a definitive answer on this, but I have always thought
that
was the case - [excerpt from quote by Larry Linson]
Click to expand...

Sorry if I wasn't clear on what kind of info I was asking for.
Click to expand...
 
Al

You may have closed your excerpt too soon ... Larry went on to say
"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."

That matches my experience, as mentioned in my previous post, that
returning a single record, via query or SQL will be much faster than
returning all records, then filtering down to a single record.
Click to expand...

Yes, I just took that smaller section, from the larger excerpt I posted
originally, to show that Larry Linson said he can't be "difinitive" - if
he had said he could say so, difinitively, then I probably wouldn't be
asking.

Thanks for confirming your experience - interestingly I was just reading
about "server filters".

Do you use filters and if so in what situations vs. using queries?
Al
 
ag said:
Yes, I just took that smaller section, from the larger excerpt I
posted originally, to show that Larry Linson said he can't be
"difinitive" - if he had said he could say so, difinitively, then I
probably wouldn't be asking.

Thanks for confirming your experience - interestingly I was just
reading about "server filters".

Do you use filters and if so in what situations vs. using queries?
Al
Click to expand...

It's important to distinguish between using a Jet (mdb) back end versus a server
back end. Filters applied to forms bound to ODBC server sources send perfectly
valid SQL statements to the server for processing so it would be just as fast as
a query. Filtering a form bound to a Jet back end might very well be a lot
slower (I don't know myself).
 
It's important to distinguish between using a Jet (mdb) back end
versus a server back end. Filters applied to forms bound to ODBC
server sources send perfectly valid SQL statements to the server for
processing so it would be just as fast as a query. Filtering a form
bound to a Jet back end might very well be a lot slower (I don't know
myself).
Click to expand...

Interestingly, related to an mdb/jet scenario, I checked to see what the
Dan Haught, Microsoft Jet Engine Programmer's Guide 2nd ed. had to say if
anything, and on pg 175 it is recommended that if the recordset contains
more than 100 records performance will suffer and the author(s) recommend
using the copyquerydef method, instead.
Al
 
Back
Top