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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
but access does not support CREATE VIEW ...
RussellMartinAuthor Commented:
Explain Your self!

In Vb (connected to an access database) this statement successfully creates a view/query

db.Execute sql   'db is an ADODB.Connection
RussellMartinAuthor Commented:
Dont bother , I found the soloution here
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
Anthony PerkinsCommented:
Also, please post the solution here.  There were two offered.
RussellMartinAuthor Commented:
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.OLEDB.4.0;DATA SOURCE=C:\Test\TICKETS.MDB;Jet OLEDBatabase Password=test"

    Set cmd = New adodb.Command
    cmd.CommandText = "Select * FROM STickets UNION Select * FROM ETickets"
    cat.Views.Append "Summary", cmd
End Sub
Closed, 125 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.