Pass-through query to Sql Server filtered locally in Access

[dk]

Hi all,

Would appreciate some advice on the following:

I am trying to speed up an Access database connected to a SQL Server
back-end. I know I can use a pass-through query to pass the sql code
directly to SQL Server and speed things up.

However, I want to be able to "filter" or place conditions on which records
to retrieve based on LOCAL tables in Access.

For example, let's say the main back-end table in SQL Server is "Sales"
which has ID fields for "Region" and "Rep". My local access tables have
"Region" and "Rep" tables with the ID and additional relevant info. My
end-users select (check-off) which regions and reps they are interested in
and the "Sales" table should return only these relevant records.

When I used a linked table to SQL and created the relationships in a query
to the local tables, this worked fine.... but was SLOOOOOOOOOOOOWWWWWWWWWWW.
Now that I've replaced the linked "Sales" table with a pass-through query,
what is the best way to have the variables people are selecting in local
tables being passed through with the query as well?

Hope this makes sense!

 

[Larry Linson]

Pick up the value from the local table's records, locally, and use that
value to construct the WHERE clause of the passthrough query.

Joining server tables and local Access tables in a query defeats the purpose
of a passthrough query... and is likely to bring back "oodles and gobs" of
data to be manipulated by the Jet DB engine.

Larry Linson
Microsoft Access MVP

 

[dk]

Thanks Larry,

If my local tables have a check box for each appropriate value (in other
words, someone checks off each "rep" or "region" they would like), how/where
can I end up storing/constructing this set (array?) of variables for use in
the WHERE clause?

Here's an example of the "rep" selection table:

ID Name Selected
1 Joe Blow -1 (ie: Yes)
2 Jane Doe 0 (ie: No)
3 Doug Hoe -1
4 Judy Low -1

Therefore, I would want records from the back-end SQL Server "Sales" table
to be returned only if they have the rep ID of 1, 3, or 4. How do I get
those into the WHERE clause?

Thanks!