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