联合选择查询中来自MS-Access的SQLite语法错误 [英] SQLite Syntax Error In Union Select Query From MS-Access
问题描述
在全新的MS Access 2010数据库中，我使用ODBC连接从SQLite数据库链接到两个表.我有以下联合查询:
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;
如果我使用sqlite3命令行应用程序对同一数据库执行此SQL，则该SQL将成功运行并返回正确的数据.当我在MS Access 2010数据库中运行查询时，出现以下错误消息:
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)
在MS Access中运行时，针对不同表的其他联合查询将获得相同的错误消息.在sqlite3命令行中运行时，它们将成功运行并返回正确的数据.
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.
推荐答案
我意识到这是一个非常老的线程，但是我刚刚遇到了这个问题，并且找到了一个非常简单的解决方案，因此，在其他任何人的情况下都值得分享有问题.尽管Access似乎无法在两个链接表上运行UNION查询，但是如果创建传递查询并在其中放置UNION的SQL，它就可以正常工作.假定SQL然后由SQLite执行，并且结果作为单个结果集返回，而不是Access本身尝试将UNION应用于两个单独的结果集.
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.
我无法在较早版本中进行测试，但是它可以在Access 2016中使用.
I am unable to test in earlier versions, but it works in Access 2016.
这篇关于联合选择查询中来自MS-Access的SQLite语法错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
