Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Using MSQuery to Link to a UNION Query (2003 sp2)

    I have 2 different union queries written in Access that both work there. I am in Excel and trying to use Import External Data - New Database Query to pull that information from the database. The smaller of the two union queries I am able to link to, but the larger one fails with a generic "can't access table" error message. Is there a limit on the size or capacity of union queries that can be pulled into Excel?
    thanks
    christine

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    Could you tell us how many records are in the larger union query?

    Also: As a troubleshoot; Is it possible to export the Union Query from Access to Excel. If you select the query in the DB window and select Analyse it with Excel in the Office Links button dropdown.
    Regards,
    Rudi

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    I exported it to Excel. It only returns 6 results. There not too many that Excel can't handle it. I also added the SQL from the query in Access. Can it be that it just cannot handle the table joins that occur on the database level? Would I have to rewrite the union using MSQuery directly to the original tables?
    thanks
    christine

  4. #4
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    Sorry, forgot to attach. Here's the file.
    Attached Files Attached Files
    thanks
    christine

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    There are many possible reasons why a query can't be imported. Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    The total length of the SQL statement of the union query is 2308 characters; perhaps that is too much for MS Query. Also see my previous reply, which was submitted as the same time as yours.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    Hey, I figured it out. Hans, your last post prompted me to think it through. The original query was too complex. MSQuery couldn't resolve around the joins because I was trying to do it outside of the database. When I nested the queries, I was able to simplify the logic. I attached a file so you could see the difference. I performed what I wanted to do in the original union as separate queries. Then I joined the results of those queries together in a union in access. I was then able to link to it from excel using MSQuery. Ah, ha! The answer as usual...simplify. Thanks again to all for the help.
    Attached Files Attached Files
    thanks
    christine

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Using MSQuery to Link to a UNION Query (2003 sp2)

    Yep, that's what I suspected. Glad you were able to solve it yourself! <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

Posting Permissions

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