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" <[e-mail]> wrote in message
news:8Y********************@news01.bloor.is.net.ca ble.rogers.com...

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!

[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!
"Larry Linson" <bo*****@localhost.not> wrote in message
news:6_*****************@nwrddc01.gnilink.net...

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" <[e-mail]> wrote in message
news:8Y********************@news01.bloor.is.net.ca ble.rogers.com...

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!

[PhilG]

dk
The best way may be to pass the required "regions" as a
comma-delimited list to the stored procedure.
Then get SLQ Server to turn the list into a table.
There is an excellent discussion of this at
http://www.algonet.se/~sommar/arrays-in-sql.html

Phil
On Wed, 19 Nov 2003 19:39:13 GMT, "dk" <[e-mail]> wrote:

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!
"Larry Linson" <bo*****@localhost.not> wrote in message
news:6_*****************@nwrddc01.gnilink.net.. .

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" <[e-mail]> wrote in message
news:8Y********************@news01.bloor.is.net.ca ble.rogers.com...

> 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 interestedin > and the "Sales" table should return only these relevant records.
>
> When I used a linked table to SQL and created the relationships in aquery > to the local tables, this worked fine.... but was

SLOOOOOOOOOOOOWWWWWWWWWWW.

> Now that I've replaced the linked "Sales" table with a pass-throughquery, > 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!
>
>