Difference in selection and ordering on RecordSource compared to using Filter and Order property (1 Viewer)

[amorosik]

Suppose we have a continuous form that displays as many rows as there are records in the CUSTOMERS table
I can restrict the display to only records using the FILTER property and sort them using ORDER
Or I can set an equivalent selection using a sql string complete with selection and sorting directly on RecordSource
Both systems are often used within the same Access procedure, following personal preferences rather than following a logic

But what are the pros and cons of the first (RecordSource with complete sql string) versus the second possibility (RecordSource + Filter + Order) ?

 

[moke123]

One difference would be the number of records loaded. Using a filter you'd load all records where with sql you'd only load what's needed.

 

[amorosik]

One difference would be the number of records loaded. Using a filter you'd load all records where with sql you'd only load what's needed.

ok, +1 per RecordSource and Sql String

 

[ebs17]

I would disagree with the previous statement. Whether I set a filter in Access or reset the SQL statement, the relevant records are reloaded in both cases.

However, Jet can use indexes. So if I set filters or entire SQL statements with a WHERE clause that are formulated to use the index, then only these are loaded, in the other case the entire table, which is then filtered locally.

If you use recordset objects: These have the loaded data fixed and keep it locally. On the basis of such an object you can create another filtered recordset object.

 

[Pat Hartman]

Both systems are often used within the same Access procedure, following personal preferences rather than following a logic

It completely depends on the size of your recordset. The larger the table, the more inefficient the load everything and filter later becomes. This is especially problematic if you ever have to convert to SQL Server. The "filter" method isn't terrible for small Jet/ACE databases but is horrible for RDBMS databases.

Most of my apps either start out as RDBMS or potentially end up that way so I never bind my forms to tables unless the tables will ALWAYS be very small. The apps either have pre-filter forms if the filtering is complex or have a couple of combos or search boxes on the edit form itself.

When working with a RDBMS it is far better to run a hundred separate queries than to download 50,000 records every time the form opens.

 

[amorosik]

Up to this moment the answers seem oriented towards demonstrating the superiority of the RecordSource loaded with a complete sql string compared to the simple table/query inside the RecordSource and the use of the Filter + Order property

 

[Josef P.]

I tested the following for linked tables to the SQL server:

• Query as the data source of a form and the desired filter conditions are built into the query.
• Data source without filter condition for the form and the required filter conditions are set via Form.Filter.

Both cases generate the identical SQL data retrieval in the SQL server via ODBC.

I recommend observing the data exchange processes of Access/ODBC/SQL-Server with the SQL-Server-Profiler / XEvent Profiler.
If you have some overview of this, you will recognize why some SQL statements (such as Access queries with many joins on linked tables) do not run as fast as others.

use of the Filter + Order property

Is it possible that the data is retrieved from the server again for each change?
In this case, the query with the customized SQL statement will win, as the data is then only retrieved once.

 

[amorosik]

+2 per RecordSource
0 per Filter+Order

 

[Josef P.]

"+ RecordSource" ... only if Filter and OrderBy are to be changed at the same time.
Otherwise the variants are equivalent.
This also applies, for example, to opening with DoCmd.OpenForm + WhereCondition parameter.

 

[CJ_London]

The where parameter is a filter, not a criteria - it just prioritises the records being filtered

 

[Josef P.]

The where parameter is a filter, not a criteria - it just prioritises the records being filtered

The WhereCondition parameter ends in the filter property of the form.
This property (or the parameter directly .. I don't know the programming - I only know the behavior according to the profiler) ensures that only these data records are retrieved from the SQL server and no others.
This is easy to check with the profiler.

Note: It is possible that the ODBC drivers of other DBMS do not behave in this way. I only took a closer look at the behavior for the SQL server drivers.

 

[CJ_London]

Also easy to turn off the filter once the form is open and see all the records - are you suggesting this would instead issue a requery of the recordsource rather than simply removing the filter?

 

[Josef P.]

I'm not suggesting anything, I'm just describing what I saw in the profiler when I tested these variants.

a) Open form with Recordsource: select ... from LinkedTable where id = 56789
+ DoCmd.OpenForm "FormWithFilteredSource"
b) Open form with Recordsource: select ... from LinkedTable
+ DoCmd.OpenForm "FormWithUnFilteredSource", , , "id = 56789"

Result (xevent profiler):
a)

b)

Note: Relevant for data retrieval are 31/32 and 53/54.

 

[CJ_London]

We may be talking at cross purposes

vba code - data source is an access table, form recordsource is the name of the table
docmd.OpenForm "nwdaEmployees",,,"EmployeeID=1"

result

Clicking the Filtered button removes the filter and exposes all the records. In my opinion the WHERE parameter is poorly named - implies a criteria but is a filter.

Alternative - use the (also poorly named) filter parameter - which actually requires the name of a query
vba code =docmd.OpenForm "nwdaEmployees",,"query48"

sql to query48

SELECT nwdaEmployees.*
FROM nwdaEmployees
WHERE (((nwdaEmployees.EmployeeID)=1))

result is identical - a filtered recordset

and again, clicking on the filtered button exposes all the records

This can have a potential use if the underlying tables are different and providing field names used on the form are consistent - but using it these days typically indicates poor table design . Only time I've used it was in the days of replication when I had a FE linked to 'master' tables and 'slave' tables and could just use the one form for a specific table, changing the filter parameter to view one or the other

In both case a potential issue if a user is only supposed to see their records - filtering needs to be disabled.

My understanding is Access prioritises the filtered records for display and user interaction and continues to load the rest of the recordset in the background. Not obvious on small recordsets but is for large ones.

Access does use a hidden query with criteria for subform recordsources. The criteria being based on the linkchild/master properties. It will be one of those queries starting with '~sq_' you can see in the msysObjects table. I've not tested whether this is actually a true criteria (i.e. fetches new data on change to the linkmaster value) or effectively another filter

 

[Josef P.]

We may be talking at cross purposes

That is quite possible.

What is a filter for you?
If Form.Filter (+FilterOn = true) is changed, the data is queried again from the source.
I only looked at this in the SQL profiler, but assume it could also be seen in the Jet Showplan
/edit: Presumably this cannot be seen in Jet showplan, as there is no adjustment required in the execution plan if only values are changed.

If I have understood you correctly, a filter for you is when the data is only presented filtered, but all data records have already been retrieved from the DBMS.
Then the Form.Filter property could also have the wrong name (if MSSQL/ODBC is used).

My understanding is Access prioritises the filtered records for display and user interaction and continues to load the rest of the recordset in the background.

Do you also mean data records that do not match the filter expression? Maybe that's what happens with Jet/ACE backends.
I do not recognize this for ODBC linked tables in the SQL server profiler.
Background loading is clearly visible in forms with a data source with several data records. Every few seconds, a data record block is reloaded via the (temporary) procedure created at the beginning (line 31/32 and 53/54 in capture form #13) - but always only on the amount of data matching the filter/criteria.

 

[CJ_London]

If I have understood you correctly, a filter for you is when the data is only presented filtered, but all data records have already been retrieved from the DBMS.

for me a filter only applies to the returned recordset. So a form with a recordsource of

SELECT *
FROM myTable
WHERE myDate>Date()

will only return those records that fit the criteria and filtering will only impact that returned recordset, removing the filter will still only show the restricted recordset per the criteria.

Do you also mean data records that do not match the filter expression? Maybe that's what happens with Jet/ACE backends.

providing you mean filter and not criteria then yes - but my understanding is it will apply to any back end used by Access, not just Jet/ACE. The exception would be using a parameter query which uses the sql syntax of the BE.

In summary - criteria is applied in the query SQL to limit the records returned. A filter is applied to that returned recordset to temporarily reduce it further. It seems to me that for you they are two words that mean the same thing

 

[Josef P.]

In summary - criteria is applied in the query SQL to limit the records returned.

Ok, the Form.Filter property or the WhereCondition from DoCmd.OpenForm also limit the records returned (Profiler info from SQL-Server see #13) - so this is no filter.

Example:
Form with datasource: SELECT ... FROM tabTest WHERE tabTest.N=5
Call by command button: Me.Filter = "T = 'abc'"
The behavior for filtering can be seen in the JetShowPlan (access backend).

Open form:
Showplan =

- Inputs to Query -
Table 'tabTest'
- End inputs to Query -

01) Restrict rows of table tabTest
      by scanning
      testing expression "tabTest.N=5"

Click on button:
Showplan =

- Inputs to Query -
Table 'tabTest'
- End inputs to Query -

01) Restrict rows of table tabTest
      by scanning
      testing expression "T='abc' And tabTest.N=5"

or with index

- Inputs to Query -
Table 'tabTest'
- End inputs to Query -

01) Restrict rows of table tabTest
      using index 'IX_TN'
      for expression "T='abc' And tabTest.N=5"

=> My conclusion: The new conditions (filter/criteria) are at least communicated to the engine. Am I wrong?

 

[CJ_London]

hmm - so what you are saying is that by applying or removing a filter results in fetching an updated recordset from the BE and not just modifying the originally fetched recordset based on the query criteria - otherwise why bother communicating with the BE?

All I can say is it doesn't match my experience.

 

[Josef P.]

That made me curious.
As I have little practice with Access backends, I started a test.
I tested the RecordsetTyp property (Dynaset and Snapshot) in the form with an Access backend:

I think I found the variant you mean: Recordsettype = Snapshot

Test scenario:
Form data source: stored query: SELECT Q.id, Q.N, Q.T, Q.D FROM tabTest AS Q WHERE (((Q.D)>#1/1/1900#)) ORDER BY Q.N, Q.id;
Click on button:

1. update N of all records to 0
2. Set Filter property N > 3

Code behind button click:

Private Sub cmdStartTest_Click()
    CurrentDb.Execute "Update tabTest set N = 0"
    Me.sfrItems.Form.ApplyFilter "N > 3"
End Sub

Public Sub ApplyFilter(ByVal NewFilterCriteria As String)
    Me.Filter = NewFilterCriteria
    Me.FilterOn = (Len(NewFilterCriteria) > 0)
    Me.txtFilterInfo.Value = NewFilterCriteria
End Sub

=>
If Dynaset is set, no records are visible immediately after FilterOn = True ... Filter: N>3 with N = 0 in all records
If the RecordsetType is set to Snapshot, the first click on the button does show records with N > 3 (=== 'filter old data' and not call new data from backend).
But: From the second (sometimes 3. or 4.) click onwards, the changes are immediately visible again.
Interestingly, however, the complete filter expression is still displayed in the showplan.

Open Form:

--- qClosedItems ---

- Inputs to Query -
Table 'tabTest'
    Database 'H:\Test\~AWF\CriteriaVsFilter\Backend.accdb'
- End inputs to Query -

01) Restrict rows of table tabTest
      using rushmore
      for expression "Q.D>#1/1/1900#"
02) Sort result of '01)'

After update records + apply Filter:

--- temp query ---

- Inputs to Query -
Table 'tabTest'
    Database 'H:\Test\~AWF\CriteriaVsFilter\Backend.accdb'
- End inputs to Query -

01) Restrict rows of table tabTest
      using rushmore
      for expression "Q.D>#1/1/1900#"
      then test expression "N>3"
02) Sort result of '01)'

Note: "--- temp query ---" instead of "--- qClosedItems ---"
=>Some records with N > 3 are visible
What is the point of (apparently) accessing the data if it is not used anyway?

2nd click (sometimes only after the 3rd or 4th click) on button:

--- temp query ---

- Inputs to Query -
Table 'tabTest'
    Database 'H:\Test\~AWF\CriteriaVsFilter\Backend.accdb'
- End inputs to Query -

01) Restrict rows of table tabTest
      using rushmore
      for expression "Q.D>#1/1/1900#"
      then test expression "N>3"
02) Sort result of '01)'

=> no records are visible


Snapshot has no effect for linked tables from SQL Server. When I tested these tables, every change was always immediately visible.

Note:
With the SQL server it is important to me: there are always rumours that all data records are fetched from the server first and then filtered when you use Form.Filter or the WhereCondition parameter. This is simply not true.

 

[Pat Hartman]

Both cases generate the identical SQL data retrieval in the SQL server via ODBC.

not sure how that is possible. One recordsource would be Select * from sometable but the other would be Select flda, fldb, fldc, from sometable where flda = 45. The first query would select potentially thousands of rows but the second selects only 1.