Form Recordsource efficiency

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

Guest

When I open a form with a recordsource (a table) and pass a filter in the
docmd.openform, does Access (2003) read the entire table or does it just read
the one record (assuming the filter column is indexed) ?? Is it more
efficient to build the recordsource with a WHERE clause in the Form open
event?.
 
mscertified said:
When I open a form with a recordsource (a table) and pass a filter in
the docmd.openform, does Access (2003) read the entire table or does
it just read the one record (assuming the filter column is indexed)
?? Is it more efficient to build the recordsource with a WHERE clause
in the Form open event?.
Click to expand...

I've never seen a difference. As far as I know the filter should be as
efficient as a query when the field is indexed.
 
When I open a form with a record-
source (a table) and pass a filter in the
docmd.openform, does Access (2003)
read the entire table or does it just read
the one record (assuming the filter
column is indexed) ?? Is it more
efficient to build the recordsource
with a WHERE clause in the Form
open event?
Click to expand...

Access and Jet are "smarter" than we often give them credit for being. Even
if you open an entire huge table, it will not read the whole table beore
displaying the information. However, I have always felt it more efficient to
replace the Record Source with SQL Statements containing a WHERE.

However, I agree with Rick Brandt that you'd have to have a lot, really a
lot, of records before you could see any difference by looking at the
screen.

Larry Linson
Microsoft Access MVP
 
No, I don't think there is any difference.

So, if you use the "where" clause of the open form, and ask for one record,
that is all that is transferred to the form.

As for using a filter, I believe the behavour is the same, but filters are
generally used when you need to "change" the existing filter. You can't
change the where clause once a form has been opened, but you can change the
filter. So, as a general rule, use the where clause.
 
Back
Top