Creating UNION Query in MS Access db
I have created a union query in access database, which works lovely jubley.
However what I now need to do is create this view programaticaly using Vb6 Ado connection.
The usual Create View syntax does not seem to allow union queries.
CREATE VIEW CommonTable as select a from table1 union select a from table2
error = -2147217900
Unions not allowed in a subquery.
Clearly Access supports the Union as I copied the select statement from the design view of the UNION Query I created using MS Access.
However what I now need to do is create this view programaticaly using Vb6 Ado connection.
The usual Create View syntax does not seem to allow union queries.
CREATE VIEW CommonTable as select a from table1 union select a from table2
error = -2147217900
Unions not allowed in a subquery.
Clearly Access supports the Union as I copied the select statement from the design view of the UNION Query I created using MS Access.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
Guy Hengel [angelIII / a3]
but access does not support CREATE VIEW ...
ASKER
Explain Your self!
In Vb (connected to an access database) this statement successfully creates a view/query
sql = "Create view RM_INSTRUCTION AS SELECT *,ID AS INSTRUCTION_UID FROM DATA"
db.Execute sql 'db is an ADODB.Connection
In Vb (connected to an access database) this statement successfully creates a view/query
sql = "Create view RM_INSTRUCTION AS SELECT *,ID AS INSTRUCTION_UID FROM DATA"
db.Execute sql 'db is an ADODB.Connection
ASKER
Aha, I learned something new.
I knew about the views.append, but not that CREATE VIEW actually is supported as from some version...
good to know.
to close the question, please request a PAQ+Refund in the community support topic area
I knew about the views.append, but not that CREATE VIEW actually is supported as from some version...
good to know.
to close the question, please request a PAQ+Refund in the community support topic area
Also, please post the solution here. There were two offered.
ASKER
here is the soloution
Sub stuff()
Dim cn As adodb.Connection
Dim cmd As adodb.Command
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = "PROVIDER=MICROSOFT.JET.OL
Set cmd = New adodb.Command
cmd.CommandText = "Select * FROM STickets UNION Select * FROM ETickets"
cat.Views.Append "Summary", cmd
End Sub
Sub stuff()
Dim cn As adodb.Connection
Dim cmd As adodb.Command
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = "PROVIDER=MICROSOFT.JET.OL
Set cmd = New adodb.Command
cmd.CommandText = "Select * FROM STickets UNION Select * FROM ETickets"
cat.Views.Append "Summary", cmd
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.