[dba-SQLServer] Continuous Forms and Filters

David,

You are both right and wrong in your hypothesis. If you apply the filter in
Access, the database sends all the rows to Access, which then processes
them. This is why Access also offers Pass-Through-Queries (PTQs). When you
create a PTQ, Access gets out of the way and sends the query directly to
the db engine, which does the heavy lifting and sends only the result set.

To execute a PTQ, you use the CALL keyword, as in:

CALL MyQuery_ptq   'no quotes required.

Given that the goal here is to allow the user to create a filtered query,
then the approach I would use is to bind the form to a PTQ that returns all
the rows; then when the user applies a filter, I would rewrite the PTQ's
SQL statement and finally do a requery on the form. Another approach would
be to write a stored procedure that accepts a number of optional
parameters, then allow the user to apply the desired filter, and then
rewrite the PTQ something along these lines:

CALL MySproc parm1, NULL, parm3, NULL, parm5

Of course, the logic within the SP would have to be able to handle the
parameters passed, and react intelligently.

Arthur


On Fri, Apr 25, 2014 at 3:16 PM, David Emerson <newsgrps at dalyn.co.nz> wrote:

> I am curious.  My understanding is that when a filter is applied this way,
> all the records are brought across and the filtering is done by Access, not
> at the SQL end.  Is this correct?
>
> Regards
>
> David Emerson
>