[dba-SQLServer] Continuous Forms and Filters

Thanks Arthur for your detailed explanation and solution suggestions - much
appreciated.

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Arthur
Fuller
Sent: Saturday, 26 April 2014 9:55 a.m.
To: Discussion concerning MS SQL Server
Subject: Re: [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
>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com