0

In a brand new MS Access 2010 database, I linked to two tables from a SQLite database using an ODBC connection. I have the following union query:

SELECT Calibration_Header.Gage_ID FROM Calibration_Header
UNION SELECT CHArchive.Gage_ID FROM CHArchive;

If I execute this SQL against the same database using the sqlite3 command line application, it runs successfully and returns the proper data. When I run the query in the MS Access 2010 database, I get the following error message:

ODBC--call failed.

near "(": syntax error (1) (#1)

Other union queries against different tables get the same error message when run in MS Access. When run in the sqlite3 command line, they run successfully and return the proper data.

user1073718
  • 1
  • 1
  • Thanks for the information. The front end database that this is targeted for will be able to use Access or SQLite as the back end database, so a pass-through query wouldn't be ideal. – user1073718 Dec 05 '11 at 16:35
  • A union query is not a standard solution for a final application. Perhaps you could say a little more about what you wish to do and someone may be able to offer a lateral solution? – Fionnuala Dec 05 '11 at 16:38
  • Records can be archived, which takes them out of the regular tables and puts them in archive tables. Several combo boxes in forms need to be able to see the Gage ID for all records, archived and not. The union query is to drive those combo boxes. I could create temp tables that get filled each time the form opens, but that is overhead I was hoping to avoid. – user1073718 Dec 06 '11 at 18:11

2 Answers2

0

I realise that this is a very old thread, but I have just had this problem and found a pretty simple solution, so thought it worth sharing in case anyone else has the problem. Although Access seems unable to run a UNION query on two linked tables, if you create a pass-through query and put the SQL for the UNION in there, it works ok. Presumably the SQL is then executed by SQLite and the results returned as a single resultset, rather than Access itself trying to apply the UNION to two separate resultsets.

I am unable to test in earlier versions, but it works in Access 2016.

Lee Hill
  • 11
  • 4
0

I suspect that a UNION SELECT is not in the standard Access vernacular. You can try implementing ANSI-92 in Access 2010 and then running your query as code, as described by Albert Kallal at http://www.utteraccess.com/forum/Create-View-Access-t1924479.html&p=1924500#entry1924500. I used these instructions to successfully create an Access "view".

DUHdley d'Urite
  • 187
  • 3
  • 15
  • UNION SELECT is standard Access. No problems there. I think the problem was that Access was having problems using Union while dealing with the external link to the source tables. In that case, the OP's idea of 'archive tables' (or staging tables as I'd call them) would work better. – PowerUser Oct 25 '12 at 18:20