Any Access / SQL gurus?

Participant

Participant

Participant

Caporegime
Joined
13 May 2003
Posts
34,562
Location
Warwickshire
I am seriously raging at this one. I keep getting the error message:

ODBC--call failed
Syntax Error: Invalid Column Specification (#0)

when trying to run a SQL query calling data via ODBC.

The query is:

Code: 
SELECT CREATION_BOM_PURCHASE_ORDERS.ACCOUNT_NAME, CREATION_BOM_PURCHASE_ORDERS.ORDER_NUMBER, CREATION_BOM_PURCHASE_ORDERS.ORDER_DATE, CREATION_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE, CREATION_BOM_POR_TRANSACTIONS.ITEM_NUMBER, CREATION_BOM_PO_DELIVERIES.POD_DEL_DATE, CREATION_BOM_PO_DELIVERIES.POD_DEL_QUANTITY
FROM (CREATION_BOM_PURCHASE_ORDERS INNER JOIN CREATION_BOM_POR_TRANSACTIONS ON CREATION_BOM_PURCHASE_ORDERS.THIS_RECORD = CREATION_BOM_POR_TRANSACTIONS.PARENT_RECORD) INNER JOIN CREATION_BOM_PO_DELIVERIES ON CREATION_BOM_POR_TRANSACTIONS.THIS_RECORD = CREATION_BOM_PO_DELIVERIES.PARENT_RECORD
WHERE (((CREATION_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE)>#1/1/2010#))
UNION ALL 
SELECT CREATION_H_BOM_PURCHASE_ORDERS.ACCOUNT_NAME, CREATION_H_BOM_PURCHASE_ORDERS.ORDER_NUMBER, CREATION_H_BOM_PURCHASE_ORDERS.ORDER_DATE, CREATION_H_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE, CREATION_H_BOM_POR_TRANSACTIONS.ITEM_NUMBER, CREATION_H_BOM_PO_DELIVERIES.POD_DEL_DATE, CREATION_H_BOM_PO_DELIVERIES.POD_DEL_QUANTITY
FROM (CREATION_H_BOM_PURCHASE_ORDERS INNER JOIN CREATION_H_BOM_POR_TRANSACTIONS ON CREATION_H_BOM_PURCHASE_ORDERS.THIS_RECORD = CREATION_H_BOM_POR_TRANSACTIONS.PARENT_RECORD) INNER JOIN CREATION_H_BOM_PO_DELIVERIES ON CREATION_H_BOM_POR_TRANSACTIONS.THIS_RECORD = CREATION_H_BOM_PO_DELIVERIES.PARENT_RECORD
WHERE (((CREATION_H_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE)>#1/1/2010#));

Massive long shot, but does anyone have any ideas what could be going on? Google reveals nothing.
 
Do the data types of all the columns in the first select match the corresponding column in the second one? ie you've not got CREATION_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE as a DATE and CREATION_H_BOM_PURCHASE_ORDERS.ORDER_DUE_DATE as a TIMESTAMP for example.
 
Thanks for the reply.

All the fields in the query are the same datatype, yes.

I've even tried using only one field and I still get the error.

Tried to replicate in MS Query but it doesn't seem to allow union queries.
 
The only thing I can find on google isn't particularly helpful...

Open the SQL pane in an Access query window and paste your UNION
statement. The compiler will give you a more detailed error message to
help you debug your SQL syntax.
Click to expand...
 
Yeah I saw that when I was Googling, but my union statement was already in the SQL pane and there was no further detail as to the error message, presumably because it's an ODBC error and not an Access error.

The strange thing is that each query works fine run on its own, but when I add 'UNION' to combine the SQL lines, it refuses. I'm thinking it's an ODBC driver problem.

Thanks for the input anyway.
 
Does anyone know of any other ways to create a single data source from multiple queries in Access? UNION surely can't be the only way?
 
Well, you could append each query separately into an empty MS Access table, but that would be a real Kludge...

Have you tried running the query using a native query tool for your target database (SQL Server, Oracle or whatever)? That may give you more error info than running the query through ODBC from Access.
 
Yes it would be a bit of a pain. Given that I only need to refresh the data quarterly, I currently have it working by importing the data manually via ODBC to Access tables and querying the resultant union query in Excel.

I still wanted it working fully automatically via linked tables, as it's just fire-and-forget that way and a bit more professional not having to re-import the data each time, but I just don't think it's going to happen with Sage 100's broken ODBC driver.

Regarding your suggestion of a native query tool, you've lost me I'm afraid...can you give me an example of a native query tool?
 
You must log in or register to reply here.
Back
Top Bottom