Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2005
    Posts
    2

    Question Unanswered: Error : Unions not allowed in subquery

    I am using ADO to create views in MS Access database. I created two views called STickets and ETickets from some tables. These two views have the same fields. Now I am trying to create another View called Summary which is a Union on the above two views. My code reads as follows :


    Dim cn As ADODB.Connection

    Set cn = New Connection

    cn.ConnectionString = "PROVIDER=MICROSOFT.JET.OLEDB.4.0;DATA SOURCE=C:\Test\TICKETS.MDB;Jet OLEDBatabase Password=test"

    cn.Open

    cn.Execute "CREATE VIEW Summary AS Select * FROM STickets UNION Select * FROM ETickets"


    I get a runtime error when I execute this code. The error is -

    Run-time error '-2147217900 (80040e14)':
    Unions not allowed in a subquery.


    I'm running VB 6 with SP6 on Windows XP with SP1. The Access database is Access 2000. Has anyone seen this problem before ? I would really appreciate it if someone can help.

    Kala.

  2. #2
    Join Date
    Feb 2005
    Posts
    4
    help yourself with storing the union in a temp table you delete after needing it. Not a nice solution, but helpful.

  3. #3
    Join Date
    Feb 2005
    Posts
    2
    Thank you Rinaku, for a quick response. I'll use your idea and let you know how it worked.

  4. #4
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    try your code like this

    Code:
    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

  5. #5
    Join Date
    Dec 2005
    Posts
    1

    Error: Unions not allowed in subquery

    Adjust your select statement like this:

    "CREATE VIEW Summary AS Select * FROM (Select * FROM STickets UNION Select * FROM ETickets)"

    That is basically selecting everything from your query result into your view.

    Phillip

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •