Creating UNION Query in MS Access db

Posted on 2006-04-25
Last Modified: 2013-12-25
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.
Question by:RussellMartin
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    but access does not support CREATE VIEW ...
    LVL 2

    Author Comment

    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
    LVL 2

    Author Comment

    Dont bother , I found the soloution here
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    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
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Also, please post the solution here.  There were two offered.
    LVL 2

    Author Comment

    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

    Accepted Solution

    Closed, 125 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Problem to refer to cell 8 68
    Window placement 17 56
    Windows 10 start screen issues 9 40
    Updates not working for MS Windows 7 12 82
    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    26 Experts available now in Live!

    Get 1:1 Help Now