469,576 Members | 1,684 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,576 developers. It's quick & easy.

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!
Nov 12 '05 #1
3 9055
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" <a@b.com> 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!

Nov 12 '05 #2
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" <a@b.com> 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!


Nov 12 '05 #3
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" <a@b.com> 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" <a@b.com> 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!
>
>



Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by Alex Nitulescu | last post: by
31 posts views Thread by Sam of California | last post: by
11 posts views Thread by kinaxx | last post: by
10 posts views Thread by Robert Dailey | last post: by
6 posts views Thread by lisp9000 | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.