Union Queries on ODBC (V FoxPro) linked tables generate error from .mdb front end.

M

Michael Edwards

I can't figure out how to get my union queries to work on linked Foxpro
tables.

Basically, I want to get the sj (Sales Journal) and sjarc (Archived Sales
Journal) tables returned in the form of a Union query.

The SQL is:
SELECT sj.sjaracct, sj.sjidno, sj.sjinv, sj.sjaccount, sj.sjpdate,
sj.sjidate, sj.sjddate, sj.sjbsdate, sj.sjbedate, sj.sjside, sj.sjlev2,
sj.sjlev3, sj.sjitype, sj.sjamount, sj.sjcontrol, sj.sjnotes, sj.sjlastd,
sj.sjlastp, sj.sjreverse, sj.sjori
FROM sj

UNION ALL SELECT sjarc.sjaracct, sjarc.sjidno, sjarc.sjinv, sjarc.sjaccount,
sjarc.sjpdate, sjarc.sjidate, sjarc.sjddate, sjarc.sjbsdate, sjarc.sjbedate,
sjarc.sjside, sjarc.sjlev2, sjarc.sjlev3, sjarc.sjitype, sjarc.sjamount,
sjarc.sjcontrol, sjarc.sjnotes, sjarc.sjlastd, sjarc.sjlastp,
sjarc.sjreverse, sjarc.sjori
FROM sjarc;

I get the following error on this and all UNION queries on the Foxpro
Tables:

"ODBC --call failed.
[Microsoft][ODBC Visual FoxPro Driver]Syntax error. (#200)"

Is there a way to do this from Access 2000?
 
M

[MVP] S.Clark

*Caution: Longshot WAG*
Create each piece of the Union as a seperate query. Then make a UNION of
those two queries.
 
M

Michael Edwards

Tried it and it doesn't work. Thanks for the longshot suggestion anyway.

[MVP] S.Clark said:
*Caution: Longshot WAG*
Create each piece of the Union as a seperate query. Then make a UNION of
those two queries.

--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

Michael Edwards said:
I can't figure out how to get my union queries to work on linked Foxpro
tables.

Basically, I want to get the sj (Sales Journal) and sjarc (Archived Sales
Journal) tables returned in the form of a Union query.

The SQL is:
SELECT sj.sjaracct, sj.sjidno, sj.sjinv, sj.sjaccount, sj.sjpdate,
sj.sjidate, sj.sjddate, sj.sjbsdate, sj.sjbedate, sj.sjside, sj.sjlev2,
sj.sjlev3, sj.sjitype, sj.sjamount, sj.sjcontrol, sj.sjnotes, sj.sjlastd,
sj.sjlastp, sj.sjreverse, sj.sjori
FROM sj

UNION ALL SELECT sjarc.sjaracct, sjarc.sjidno, sjarc.sjinv,
sjarc.sjaccount,
sjarc.sjpdate, sjarc.sjidate, sjarc.sjddate, sjarc.sjbsdate,
sjarc.sjbedate,
sjarc.sjside, sjarc.sjlev2, sjarc.sjlev3, sjarc.sjitype, sjarc.sjamount,
sjarc.sjcontrol, sjarc.sjnotes, sjarc.sjlastd, sjarc.sjlastp,
sjarc.sjreverse, sjarc.sjori
FROM sjarc;

I get the following error on this and all UNION queries on the Foxpro
Tables:

"ODBC --call failed.
[Microsoft][ODBC Visual FoxPro Driver]Syntax error. (#200)"

Is there a way to do this from Access 2000?
Click to expand...
Click to expand...
 
M

Michael Edwards

Thanks Cindy, but I already tried that solution and get error message
"Reserved Error (-7778); there is no message for this error." No good.
 
M

Michael Edwards

Ok, I got this far in that MSDN KB article solves some of the problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;212886

So I get it to work more or less by pointing to a Machine Data Source when
prompted after trying to open the pass through query. Isn't there a way to
open the querie without having to search and choose the Machine Data Source
each time the query is opened? I can't have users searching through the
Machine Data Source list each time one of these queries is called.

Am I missing something here? Is there a way to attach the Machine Data
Source to the Pass Through Query?
 
M

Michael Edwards

I fixed my problem, I had to create a new pass through query and put the
union query SQL in the window, then I opened the properties for the query
and used the connection string wizard to create the connection string
pointing to the newly created machine data source. It would not work using
the Microsoft Visual Fox Pro ODBC Driver alone. I created a machine Data
Source using the FoxPro ODBC Driver and the database directory location.
Problem solved. But what an eye opener.

Michael Edwards said:
Ok, I got this far in that MSDN KB article solves some of the problem
http://support.microsoft.com/default.aspx?scid=kb;en-us;212886

So I get it to work more or less by pointing to a Machine Data Source when
prompted after trying to open the pass through query. Isn't there a way to
open the querie without having to search and choose the Machine Data Source
each time the query is opened? I can't have users searching through the
Machine Data Source list each time one of these queries is called.

Am I missing something here? Is there a way to attach the Machine Data
Source to the Pass Through Query?

Michael Edwards said:
I can't figure out how to get my union queries to work on linked Foxpro
tables.

Basically, I want to get the sj (Sales Journal) and sjarc (Archived Sales
Journal) tables returned in the form of a Union query.

The SQL is:
SELECT sj.sjaracct, sj.sjidno, sj.sjinv, sj.sjaccount, sj.sjpdate,
sj.sjidate, sj.sjddate, sj.sjbsdate, sj.sjbedate, sj.sjside, sj.sjlev2,
sj.sjlev3, sj.sjitype, sj.sjamount, sj.sjcontrol, sj.sjnotes, sj.sjlastd,
sj.sjlastp, sj.sjreverse, sj.sjori
FROM sj

UNION ALL SELECT sjarc.sjaracct, sjarc.sjidno, sjarc.sjinv, sjarc.sjaccount,
sjarc.sjpdate, sjarc.sjidate, sjarc.sjddate, sjarc.sjbsdate, sjarc.sjbedate,
sjarc.sjside, sjarc.sjlev2, sjarc.sjlev3, sjarc.sjitype, sjarc.sjamount,
sjarc.sjcontrol, sjarc.sjnotes, sjarc.sjlastd, sjarc.sjlastp,
sjarc.sjreverse, sjarc.sjori
FROM sjarc;

I get the following error on this and all UNION queries on the Foxpro
Tables:

"ODBC --call failed.
[Microsoft][ODBC Visual FoxPro Driver]Syntax error. (#200)"

Is there a way to do this from Access 2000?
Click to expand...
Click to expand...
 
C

Cindy Winegarden

Hi Mike,

I'm so glad you found a solution to your problem. Hopefully this thread will
benefit someone else who is searching the archives.
 
Top