Performance: Filter VS Where Clause

In MS Access there are 2 ways to specific the records to be fetched. One is using Filter and another is using where clause query.

Filter:
Pros:

• Build into the MS Access form, report, query or datasheet. It is easy to be applied and removed by users

Cons:

• The whole recordset of data must be loaded into memory first, before the filter could be applied.

Where Clause:
Pros:

• The subset of records are loaded into memory. This will be faster when dealing with huge records of data.

Cons:

• Not build into MS Access forms, there is a need to manually applied i.e. VBA coding. There are more work for developer to have something equivalently to Filter.

When to use Filter and when to use Where Clause

If tables, queries, reports, datasheets contain many records of data, then it is better to apply the where clause.
But, if there are only a few records, then filter might be a good idea as it is more flexible and easier to applied for both users and developers.