Union Query Lexical Error

G

Guest

I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the ODBC
program as all of my queries work fine, and it doesn't matter which fields I
try--I still get the same error. Also, if I delete 1 half of my Union Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
 
D

david epsom dot com dot au

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)
 
G

Guest

Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All of
my other queries and tables work with the ODBC...

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

PPCO said:
I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the ODBC
program as all of my queries work fine, and it doesn't matter which fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
 
D

david epsom dot com dot au

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

(david)

PPCO said:
Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All
of
my other queries and tables work with the ODBC...

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

PPCO said:
I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the
ODBC
program as all of my queries work fine, and it doesn't matter which
fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I'm using Access 2000. I am using a union-query, not a pass-through query.
I'm not sure what Ansi 92 compatible syntax is...
As far as the kind of ODBC driver: It's QODBC version 6.0. It's read-only
software. I don't know if that answers your question? Thanks!

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

(david)

PPCO said:
Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All
of
my other queries and tables work with the ODBC...

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the
ODBC
program as all of my queries work fine, and it doesn't matter which
fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Access is generating SQL which is not accepted by your
QODBC driver. I am not familiar with QODBC.

Except in very rare circumstances, Access generates
standard ODBC SQL. If your QODBC driver can't
handle ODBC, that is a good reason not to use QODBC.

But if your database can handle Union Queries, perhaps
you could use a pass-through query to pass the union
query through to whatever you are connecting to, avoiding
the problem with your QODBC driver.

What kind of a database does a QODBC driver connect
to? What is the data request language of that database?

(david)


PPCO said:
I'm using Access 2000. I am using a union-query, not a pass-through query.
I'm not sure what Ansi 92 compatible syntax is...
As far as the kind of ODBC driver: It's QODBC version 6.0. It's read-only
software. I don't know if that answers your question? Thanks!

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

(david)

PPCO said:
Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All
of
my other queries and tables work with the ODBC...

:

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the
ODBC
program as all of my queries work fine, and it doesn't matter which
fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I'm not sure if I understand about Access generating SQL that is not
understood by QODBC. I'm not trying to write to the ODBC file, only trying to
read it. I've already pulled the info I need into tables and then queries.
Now I'm trying to combine the queries. The Q in QODBC stands for
Quickbooks(our accounting program) I think--the extension on the data file is
..qbi. What's strange is that if I run each half of my union query SQL, it
works fine. It's when I try to combine them that I get that error. I know
that when I run the QODBC program, it converts all the info from Quickbooks
into tables. That probably just muddies the waters...

david@epsomdotcomdotau said:
Access is generating SQL which is not accepted by your
QODBC driver. I am not familiar with QODBC.

Except in very rare circumstances, Access generates
standard ODBC SQL. If your QODBC driver can't
handle ODBC, that is a good reason not to use QODBC.

But if your database can handle Union Queries, perhaps
you could use a pass-through query to pass the union
query through to whatever you are connecting to, avoiding
the problem with your QODBC driver.

What kind of a database does a QODBC driver connect
to? What is the data request language of that database?

(david)


PPCO said:
I'm using Access 2000. I am using a union-query, not a pass-through query.
I'm not sure what Ansi 92 compatible syntax is...
As far as the kind of ODBC driver: It's QODBC version 6.0. It's read-only
software. I don't know if that answers your question? Thanks!

david epsom dot com dot au said:
What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

(david)

Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All
of
my other queries and tables work with the ODBC...

:

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the
ODBC
program as all of my queries work fine, and it doesn't matter which
fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

I just talked with the QODBC support, and they said that Union Queries are
not supported in this version of QODBC...so that answers the question. Thanks
for your efforts!

PPCO said:
I'm not sure if I understand about Access generating SQL that is not
understood by QODBC. I'm not trying to write to the ODBC file, only trying to
read it. I've already pulled the info I need into tables and then queries.
Now I'm trying to combine the queries. The Q in QODBC stands for
Quickbooks(our accounting program) I think--the extension on the data file is
.qbi. What's strange is that if I run each half of my union query SQL, it
works fine. It's when I try to combine them that I get that error. I know
that when I run the QODBC program, it converts all the info from Quickbooks
into tables. That probably just muddies the waters...

david@epsomdotcomdotau said:
Access is generating SQL which is not accepted by your
QODBC driver. I am not familiar with QODBC.

Except in very rare circumstances, Access generates
standard ODBC SQL. If your QODBC driver can't
handle ODBC, that is a good reason not to use QODBC.

But if your database can handle Union Queries, perhaps
you could use a pass-through query to pass the union
query through to whatever you are connecting to, avoiding
the problem with your QODBC driver.

What kind of a database does a QODBC driver connect
to? What is the data request language of that database?

(david)


PPCO said:
I'm using Access 2000. I am using a union-query, not a pass-through query.
I'm not sure what Ansi 92 compatible syntax is...
As far as the kind of ODBC driver: It's QODBC version 6.0. It's read-only
software. I don't know if that answers your question? Thanks!

:

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

(david)

Access definitely works with the ODBC driver as I've done several other
projects with it. It's something to do with the Union Query and ODBC. All
of
my other queries and tables work with the ODBC...

:

What kind of server or odbc driver are you using?

What version of Access are you using, is this a pass-through
query, and are you using Ansi 92 compatible syntax?

Access tries to convert Access (Jet) SQL to standard
ODBC SQL. Any ODBC standard driver should be able
to recognise standard ODBC SQL.

(david)

I am using an union query to combine 2 queries. My SQL is this so far:

SELECT [shipdate], [CustomerRefFullName]
FROM [qrysalesorder]

UNION ALL SELECT [shipdate], [CustomerRefFullName]
from [qryinvoiceline];

I am using this with an ODBC program that is pulling info from our
accounting software.

The error I'm getting is this:
"ODBC--CALL FAILED
[ODBC] Expected Lexical Element not found: <keyword> (#11015)

I tried combining the original tables, and it gives me the same error
message. It seems that it is a problem with the union query and the
ODBC
program as all of my queries work fine, and it doesn't matter which
fields
I
try--I still get the same error. Also, if I delete 1 half of my Union
Query
SQL, it works fine. Anybody know anything about getting union
queries to work with an ODBC program? Thanks!
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Union Query Error 3
Prevent Duplicates from Union Query 2
Union Query Problem 6
Union query 5
Data Mismatch in Excel 2010 4
UNION query question 7
Using "like" query from and external link to Access 1
Recordset too large? 10

Top