ODBC - Call Failed

KPLewisBPM

In MS Access I linked 2 tables from Navision Attain 310A that hold similar data.

I then created a query for each linked table to show only the fields relevant. I then created a UNION SQL statement.

When running the SQL statement

SELECT No_,Description,Quantity FROM [Order Details]
UNION
SELECT No_,Description,Quantity FROM [Finished Order Details]

Error Message: ODBC-Call Failed [Navision Software a/s][Navision Attain ODBC Driver]Expected lexical element not found:-,keyword.(#1015)

However running just the SELECT statement lines seperately works fine.

Any ideas would be greatly appreciated

Regards

KPLewisBPM

dick11

I am not certain how you did it in Access, but if I open a ODBC connection using the keyword "union" that does not work.
See the ODBC documenten. It uses the "," such as

SELECT t1.Field1,t1.field2,t2.field1 FROM Table1 t1,Table2 t2 WHERE t1.id1 = t2.id1

Success

KPLewisBPM

There is no data stored in access, I am using Linked table manager.

I have 2 table links.

When I query each table using a select statement, ODBC kicks in and goes to Navision for the information. However if i create a query that calls for data from 2 tables that is when I get the error.

The records have identical data and i need the query to bring back all records from both tables.

dick11

I have the Dutch version and could not find this program (how is it translated?) But I tried it via "Query Wizard" (translated). It works!
I use Access 2000 and NF 3.60. Perhaps that is the problem?

Other solution could be to make a small program in VBA and import that all in a real table in Access

KPLewisBPM

Linked table manager is part of MS Access it enables you to create a link to an external table.

Running a query against one linked table works fine. but to return all the records from 2 tables into 1 query is the problem.

I need to look up information based on a production order number. however when the prod order is finished it dissappears from one table and is inserted into another (Within Navision). But I need to find information for all orders, hence the reason i need all records in one query.

Kind Regards

dick11

Yes, I have done it the wright way.

Is the problem solved if you update your NF (to for example 3.60) (on a test machine)?

KPLewisBPM

Do you mean the Client or Server Software?

The problem i have is we cannot change our software, our database is bespoke to our entire organisation. all companies have to run 310A at present.

One of the reasons I am trying to get information collated in Access is I am finding so many Keys and links missing in navision,creating reports is almost immpossible.

dick11

Both.
Just copy the database files on a test machine. Perhaps disconnect this PC from your netwerk. Install the server and client software. Start the database, start a client and if he ask you to convert say "yes". You have a newer version to play with.

If that is no option then you have make something nice in Access