CODEWITHSUNDEEP
sqlitems-access
user1073718
user1073718

Reputation: 1

SQLite Syntax Error In Union Select Query From MS-Access

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.

Upvotes: 0

Views: 1295

Answers (2)

Lee Hill
Lee Hill

Reputation: 11

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.

Upvotes: 0

DUHdley d'Urite
DUHdley d'Urite

Reputation: 187

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".

Upvotes: 0

Related Questions