UNION statement doesn't work

[loRdK]

Hi,

I have a database stored in MS SQL 2000 and an application written in
VB5, which connects the database via JET/ODBC.

I have a problem with the UNION statement.

When I run a simple query like:

"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"

I get the following error:

"Runtime error 3078 - The Microsoft Jet database engine cannot find the
input table or query 'select field1 from table1'. Make sure it exists
and that its name is spelled correctly."

I can run the queries separately "SELECT field1 FROM table1" and "SELECT
field1 FROM table2", so that I'm sure table and field names are correct
and I have permission to access them.

Both field1 and field2 are the same type (int).

If I run the query in MS SQL Query Analyzer, it works fine.

It doesn't work only when I run it from VB/JET/ODBC.

Has anyone already had this kind of problem?

Any help will be highly appreciated!

Thank you so much for the attention.
--
Posted via http://dbforums.com

[Ron L.]

"loRdK" <me*********@dbforums.com> wrote in message
news:33****************@dbforums.com...

Hi,

I have a database stored in MS SQL 2000 and an application written in
VB5, which connects the database via JET/ODBC.

I have a problem with the UNION statement.

When I run a simple query like:

"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"

I get the following error:

"Runtime error 3078 - The Microsoft Jet database engine cannot find the
input table or query 'select field1 from table1'. Make sure it exists
and that its name is spelled correctly."

I can run the queries separately "SELECT field1 FROM table1" and "SELECT
field1 FROM table2", so that I'm sure table and field names are correct
and I have permission to access them.

Both field1 and field2 are the same type (int).

If I run the query in MS SQL Query Analyzer, it works fine.

It doesn't work only when I run it from VB/JET/ODBC.

Has anyone already had this kind of problem?

Any help will be highly appreciated!

Thank you so much for the attention.

Isn't Jet Access related? Are you sure the database is SQL Server? If so, is
your ODBC DSN defined correctly?

Ron.
--
Performance Intelligence, Inc.
Spy 4 DB2 - http://www.pireporting.com/spy4db2.html