Pass Through Query Help!!!

[tkosel]

Perhaps someone can enlighten me. I am relatively new at creating SQL from
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.

SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;


The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.

SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;

The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?

Thanks for helping a rookie out!

 

[JimS]

The passthrough query passes the exact sql you generate to the backend
database. It does no interpretation. Thus, it passes the
[Forms]![MainMenu]... verbage straight through. SQL 200 know nothing of your
front-end forms. You will need to interpret the fields prior to passing them
onto SQL2000.

I typically use passthrough queries for the starting point of other queries.
Thus, I'd have a query called qryV_DATA that is pretty much "Select * from
V_Data". By passing this simple query to the backend, it prevents Access
(Jet) from using ODBC to bring a row at a time across from the backend, and
instead brings the entire table across. ODBC is slow, while SQL2000 is
presumably faster on its own DB.

Once the passthrough query is there, I can substitute qryV_Data for
everywhere I put V_Data earlier. Sounds dumb, but it works, at least when I
was querying an obscure dbms with large tables.

I also passed through a query that "flattened" the tables for export to
excel. Something like "Select Name, Workdate from tblNames inner join
tblWorkSchedule on tblNames.EmployeeID = tblWorkSchedule.EmployeeID" It was
lightning fast by comparison.

The only way you could pass the criteria as far as I can tell (and I'm no
expert) is to parameterize the query, or generate the query using VBA, then
pass it to the backend. Others will have to help with those.

Hope I've been some help.
--
Jim

Perhaps someone can enlighten me. I am relatively new at creating SQL from
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.

SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;


The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.

SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;

The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?

Thanks for helping a rookie out!

 

[JimS]

One more thing.
You should always search before posting. I found this:
http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

When searching for "parameter passthrough query". It details what can and
cannot be done, and what the MVPs recommend. It'll save you lots of time.
--
Jim

Perhaps someone can enlighten me. I am relatively new at creating SQL from
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.

SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;


The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.

SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;

The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?

Thanks for helping a rookie out!

 

[tkosel]

Jim,

Thanks for your help, you led me down the path, I have figured it out with
your help. By the way, I did search and look at all the posts I found, but I
did NOT search on the same keywords that you used! I would have never
thought of "parameter passthrough query" as the keywords. Anyhow, thanks a
lot for your assistance, you were very helpful and I am forging ahead. By
the way, the passthrough query is at least 10 times faster than a normal
access query on the linked table! Its great!

One more thing.
You should always search before posting. I found this:
http://www.microsoft.com/office/com...8ada&catlist=&dglist=&ptlist=&exp=&sloc=en-us

When searching for "parameter passthrough query". It details what can and
cannot be done, and what the MVPs recommend. It'll save you lots of time.
--
Jim

Perhaps someone can enlighten me. I am relatively new at creating SQL from
scratch. If it gets too complex, I get stuck. I have a DB that uses linked
tables from a SQL 2000 Backend. I have a query that gets the data I want.
The SQL from that Query is below.

SELECT dbo_PRODUCT.PROD_ID, dbo_PRODUCT.PROD_NAME, dbo_CHAR_SETUP.CHAR_ID,
dbo_CHAR_SETUP.CHAR_NAME, dbo_V_DATA.VAR_VALUE, dbo_SG_DATA.COLLECT_TS,
dbo_V_DATA.PIECE_NMBR, dbo_SETUP.SETUP_NAME, dbo_SETUP.SETUP_ID
FROM (((dbo_PRODUCT INNER JOIN dbo_CHAR_SETUP ON dbo_PRODUCT.PROD_ID =
dbo_CHAR_SETUP.PROD_ID) INNER JOIN dbo_V_DATA ON dbo_CHAR_SETUP.CHAR_ID =
dbo_V_DATA.CHAR_ID) INNER JOIN dbo_SETUP ON dbo_CHAR_SETUP.PROD_ID =
dbo_SETUP.PROD_ID) INNER JOIN dbo_SG_DATA ON dbo_V_DATA.SG_ID =
dbo_SG_DATA.SG_ID
WHERE (((dbo_PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((dbo_CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((dbo_SG_DATA.COLLECT_TS) Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))
ORDER BY dbo_SG_DATA.COLLECT_TS, dbo_V_DATA.PIECE_NMBR;


The DB is getting fairly large. As a result, the Query is taking longer and
longer to
process. I understand that a Pass Through Query will probably speed things
up! Cool! So, fooling around a little, I came up with the following pass
through SQL.

SELECT PRODUCT.PROD_ID, PRODUCT.PROD_NAME, CHAR_SETUP.CHAR_ID,
CHAR_SETUP.CHAR_NAME,
V_DATA.VAR_VALUE, SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR, SETUP.SETUP_NAME,
SETUP.SETUP_ID FROM (((PRODUCT INNER JOIN CHAR_SETUP ON PRODUCT.PROD_ID =
CHAR_SETUP.PROD_ID)
INNER JOIN V_DATA ON CHAR_SETUP.CHAR_ID = V_DATA.CHAR_ID) INNER JOIN SETUP
ON
CHAR_SETUP.PROD_ID = SETUP.PROD_ID) INNER JOIN SG_DATA ON V_DATA.SG_ID =
SG_DATA.SG_ID
WHERE (((PRODUCT.PROD_ID)=[Forms]![MainMenu]![ProductID]) AND
((CHAR_SETUP.CHAR_ID)=[Forms]![MainMenu]![CharacteristicName]) AND
((SG_DATA.COLLECT_TS)
Between [Forms]![MainMenu]![StartDate] And
[Forms]![MainMenu]![EndDate]))ORDER
BY SG_DATA.COLLECT_TS, V_DATA.PIECE_NMBR;

The first thing I realized when trying it is that the dbo_ designation
needed to be dropped. To make a long story short, this query above does not
work. It doesn't' seem like the ! and references to fields on forms! Is
there a way I can use this criteria (form) in the SQL Query?

Thanks for helping a rookie out!