SQLite Syntax Error In Union Select Query From MS-Access

Question

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.

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
– user1073718, Commented Dec 5, 2011 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
– Fionnuala, Commented Dec 5, 2011 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
– user1073718, Commented Dec 6, 2011 at 18:11

DUHdley d'Urite · Accepted Answer · 2012-02-15 18:50:00Z

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

answered Feb 15, 2012 at 18:50

DUHdley d'Urite

1873 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

PowerUser Over a year ago

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.

Lee Hill · Accepted Answer · 2018-11-02 14:04:30Z

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.

answered Nov 2, 2018 at 14:04

Lee Hill

114 bronze badges

Collectives™ on Stack Overflow

SQLite Syntax Error In Union Select Query From MS-Access

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related