How to se filter or recordsource

[johnhawk]

I populate many of my MS Access forms with only one record from a single table. Is it faster/better to use a SQL statement (with appropriate WHERE statement) applied directly to the recordsource or point the recordsource to the appropriate table and set the filter each time the form opens? Does the version (2003 vs. 2007) or the use of a SQL back-end make a difference?

[NeoPa]

I don't believe there is ever much of a performance difference between the two approaches. I'd always recommend the full table with a filter though, for flexibility as well as ease of maintenance.

Welcome to Bytes!

[johnhawk]

Thanks

Thanks; I was under the impression that the recordsource approach would be processed by the server and the filter would be processed by the client? Hence, the former approach would limit network traffic??

[Jim Doherty]

Thanks; I was under the impression that the recordsource approach would be processed by the server and the filter would be processed by the client? Hence, the former approach would limit network traffic??

How many times do we see
"Select * from the largest table in the universe" across the network and filter it when it gets to the client
as opposed to
"Select only the columns you need and the number of rows that you can define as needing from the largest table in the universe where my criteria equals an indexed column"

For the most part with small stuff you don't notice much difference :)

[johnhawk]

I populate many of my MS Access forms with only one record from a single table. Is it faster/better to use a SQL statement (with appropriate WHERE statement) applied directly to the recordsource or point the recordsource to the appropriate table and set the filter each time the form opens? Does the version (2003 vs. 2007) or the use of a SQL back-end make a difference?

Thanks for the quick reply.

[NeoPa]

johnhawk: Thanks; I was under the impression that the recordsource approach would be processed by the server and the filter would be processed by the client? Hence, the former approach would limit network traffic??

Just to be clear - that is not what is going on here with filtered forms. It's all handled by the SQL engine.

[johnhawk]

Just to be clear - that is not what is going on here with filtered forms. It's all handled by the SQL engine.

Thanks, again. Makes sense.