访问:问题通过 VBA 创建 SQL 视图 - 子查询中不允许使用联合 [英] Access: Issue Creating SQL View Through VBA - Unions not allowed in subquery

问题描述

首先，让我用迄今为止在我的几篇博文中几乎总是说的一句话作为开头，那就是我不是 IT/技术人员.我是一名会计师，喜欢涉足 SQL 等等，但对 VBA 的了解很少，所以如果这是一个超级简单的问题或者已经被涵盖，我提前道歉.但我找不到与此特定错误直接相关的任何内容.

First of all, let me preface this with something I almost always say in my few posts so far, which is that I'm not an IT/tech person. I'm an accountant who likes to dabble a little bit in SQL and so forth but with very minimal knowledge of VBA, so I apologize in advance if this is a super-easy question or it's already been covered. But I was not able to find anything directly related to this specific error.

尝试使用 MS Access 从包含联合的 SQL 创建视图并收到以下错误:运行时错误-2147217900 (800040e14)":子查询中不允许使用联合.代码如下:

Trying to use MS Access to create a view from SQL that includes a Union and getting the following error: Run-time error '-2147217900 (800040e14)': Unions not allowed in a subquery. Code is as follows:

   Sub Create_View()
    Dim conn As ADODB.Connection
    Set conn = CurrentProject.Connection
    conn.Execute "CREATE VIEW Test_VW AS SELECT A.Unit as Unit, A.Spend as Spend, A.Date as Date, A.Type as Type FROM Table1 A UNION ALL SELECT B.Unit as Unit, B.Spend as Spend, B.Date as Date, B.Type as Type FROM Table2 B;"
            Application.RefreshDatabaseWindow 
End Sub

这是现在的代码，但我也尝试过 UNION 而不是 UNION ALL，SELECT * FROM 两个表而不是单个列(两个表都只是列出的 4 个列，它们也是相同的数据类型)，并且列和表没有别名.

This is the code as it is right now, but I've also tried UNION instead of UNION ALL, SELECT * FROM both tables as opposed to the individual columns (both tables are only the 4 listed columns, which are also the same data type), and with the columns and tables unaliased.

我应该指出的可能是问题所在，但我认为这不是表格"的问题.代码中实际上是我在没有 VBA 的情况下在 Access 中创建的视图(就像在常规 SQL 中一样).这些现在显示为查询".在数据库中.但是，我确实使用定义视图的初始 SQL 编写了 VBA，并且返回了相同的错误.所以我不认为这是问题所在.

Something I should point out that maybe could be the issue, but I wouldn't think it is is that the "Tables" in the code are actually views that I created in Access without VBA (as in just regular SQL). These now appear as "Queries" in the database. However, I did write the VBA with the initial SQL that's defining the views, and I returned the same error. So I don't think that's the issue.

另外，我应该指出，为了测试，我能够从同一个 VBA 为没有 UNION 的顶级查询创建一个视图.

Also, I should point out that to test, I was able to create a view from the same VBA for the top level query without the UNION.

无论如何，我不确定子查询在 SQL 中的哪个位置，所以可能是语法错误?

At any rate, I'm not sure where the subquery is in the SQL, so maybe it's a syntax error?

任何帮助将不胜感激.谢谢！

Any help would be greatly appreciated. Thanks!

推荐答案

QueryDefs 可以处理 UNION.不需要别名字段和表名.

QueryDefs can handle UNION. Alias field and table names are not needed.

Sub test()
Dim qdf As DAO.QueryDef
Set qdf = CurrentDb.CreateQueryDef("TestVW", "SELECT Unit, Spend, Date, Type FROM Table1 " & _
                                    "UNION ALL SELECT Unit, Spend, Date, Type FROM Table2;"
Application.RefreshDatabaseWindow
End Sub

强烈建议不要使用 Date 等保留字作为名称.

Strongly advise not to use reserved words like Date as names.

这篇关于访问:问题通过 VBA 创建 SQL 视图 - 子查询中不允许使用联合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！