Union Query problem after upgrade link to Foxpro

[KSH]

I have the following union query that worked when the customers files were in
dbase III and the program was in Access97(did not have to use ODBC). They
recently upgraded the files to Visual Foxpro and I upgraded my Access program
to 2000. Now when I use this query I get an ODBC fail error. I know it is
on the UNION ALL section because I can eliminate that part and the query
runs. But I can open the SoyTrn03 table no problem, so I know I am linked
correctly.

Any advice would be appreciated. Not sure if the problem is with the Access
upgrade or the Foxpro


SELECT SOTRAN03.ITEM, SOTRAN03.PRICE, SOTRAN03.CLASS, SOTRAN03.ORDATE,
SOTRAN03.QTYSHP, SOTRAN03.DISC, SOTRAN03.SONO, SOTRAN03.SALESMN,
SOTRAN03.QTYORD, SOTRAN03.CURRENT
FROM SOTRAN03
WHERE (((SOTRAN03.ITEM)="MISC") AND ((SOTRAN03.PRICE)>0) AND
((SOTRAN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]) AND ((SOTRAN03.CURRENT) Is Null)) OR
(((SOTRAN03.PRICE)>0) AND ((SOTRAN03.CLASS)=" 1" Or (SOTRAN03.CLASS)=" 2")
AND ((SOTRAN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]) AND ((SOTRAN03.CURRENT) Is Null))



UNION ALL SELECT SOyTRN03.ITEM, SOyTRN03.PRICE, SOyTRN03.CLASS,
SOyTRN03.ORDATE, SOyTRN03.QTYSHP, SOyTRN03.DISC, SOyTRN03.SONO,
SOyTRN03.SALESMN, SOyTRN03.QTYORD, SOyTRN03.CURRENT
FROM SOyTRN03
WHERE (((SOyTRN03.ITEM)="MISC") AND ((SOyTRN03.PRICE)>0) AND
((SOyTRN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate])) OR (((SOyTRN03.PRICE)>0) AND
((SOyTRN03.CLASS)=" 1" Or (SOyTRN03.CLASS)=" 2") AND ((SOyTRN03.ORDATE)
Between [Forms]![ReportMenu]![fromdate] And [Forms]![ReportMenu]![todate]));

 

[[MVP] S.Clark]

Warning: Shot in the dark. I have no VF experience.

1. Union queries are typically very slow.
2. ODBC connections have a timeout setting.

Any chance that increasing the connection timeout will give the UNION query
more time to finish executing, thus preventing the error?

Can you do two append queries, to push the data from these sources into a
single table, then query just the destination table instead?

Let me know if I pinned the tail on the donkey.

 

[KSH]

Steve,

Thx for your quick response, I am not sure where I set the timeout for the
ODBC connection can you shed some light on that? But, I get the error
message quickly and I also do the following union query and I have no problem
with this one:

SELECT IIf(SOTRAN03.PRICE=0,"y","N") AS PRICEfLAG, SOTRAN03.SONO,
SOTRAN03.CUSTNO, SOTRAN03.ITEM, SOTRAN03.DESCRIP, SOTRAN03.PRICE,
SOTRAN03.QTYSHP, SOTRAN03.SHIPDATE,
SOTRAN03.SHIPQTY,IIf(IsNull([lookupitem]),[item],[lookupitem]) AS mainitem,
DLookUp("calecon","crossreferencepartno","lennox = '" & [item] & "'") AS
Lookupitem
FROM SOTRAN03
WHERE (((SOTRAN03.SHIPDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]))

UNION SELECT IIf(SOYTRN03.PRICE=0,"y","N") AS PRICEfLAG, SOYTRN03.SONO,
SOYTRN03.CUSTNO, SOYTRN03.ITEM, SOYTRN03.DESCRIP, SOYTRN03.PRICE,
SOYTRN03.QTYSHP, SOYTRN03.SHIPDATE,
SOYTRN03.SHIPQTY,IIf(IsNull([lookupitem]),[item],[lookupitem]) AS mainitem,
DLookUp("calecon","crossreferencepartno","lennox = '" & [item] & "'") AS
Lookupitem
FROM SOyTRN03
WHERE (((SOYTRN03.SHIPDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]));

The only difference I see is that the one that doesn't work uses Union All
Select, but even if I remove that it still fails.
--
KSH

Warning: Shot in the dark. I have no VF experience.

1. Union queries are typically very slow.
2. ODBC connections have a timeout setting.

Any chance that increasing the connection timeout will give the UNION query
more time to finish executing, thus preventing the error?

Can you do two append queries, to push the data from these sources into a
single table, then query just the destination table instead?

Let me know if I pinned the tail on the donkey.

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

I have the following union query that worked when the customers files were
in
dbase III and the program was in Access97(did not have to use ODBC). They
recently upgraded the files to Visual Foxpro and I upgraded my Access
program
to 2000. Now when I use this query I get an ODBC fail error. I know it
is
on the UNION ALL section because I can eliminate that part and the query
runs. But I can open the SoyTrn03 table no problem, so I know I am linked
correctly.

Any advice would be appreciated. Not sure if the problem is with the
Access
upgrade or the Foxpro


SELECT SOTRAN03.ITEM, SOTRAN03.PRICE, SOTRAN03.CLASS, SOTRAN03.ORDATE,
SOTRAN03.QTYSHP, SOTRAN03.DISC, SOTRAN03.SONO, SOTRAN03.SALESMN,
SOTRAN03.QTYORD, SOTRAN03.CURRENT
FROM SOTRAN03
WHERE (((SOTRAN03.ITEM)="MISC") AND ((SOTRAN03.PRICE)>0) AND
((SOTRAN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]) AND ((SOTRAN03.CURRENT) Is Null)) OR
(((SOTRAN03.PRICE)>0) AND ((SOTRAN03.CLASS)=" 1" Or (SOTRAN03.CLASS)=" 2")
AND ((SOTRAN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]) AND ((SOTRAN03.CURRENT) Is Null))



UNION ALL SELECT SOyTRN03.ITEM, SOyTRN03.PRICE, SOyTRN03.CLASS,
SOyTRN03.ORDATE, SOyTRN03.QTYSHP, SOyTRN03.DISC, SOyTRN03.SONO,
SOyTRN03.SALESMN, SOyTRN03.QTYORD, SOyTRN03.CURRENT
FROM SOyTRN03
WHERE (((SOyTRN03.ITEM)="MISC") AND ((SOyTRN03.PRICE)>0) AND
((SOyTRN03.ORDATE) Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate])) OR (((SOyTRN03.PRICE)>0) AND
((SOyTRN03.CLASS)=" 1" Or (SOyTRN03.CLASS)=" 2") AND ((SOyTRN03.ORDATE)
Between [Forms]![ReportMenu]![fromdate] And
[Forms]![ReportMenu]![todate]));