Access: Issue Creating SQL View Through VBA – Unions not allowed in subquery

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.

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

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.

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.

EDIT: 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.

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!

Answer

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

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

Advertisement

Answer