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

Topic

New Reply

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?

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

Sorry, forgot to attach. Here’s the file.

Reply | Quote

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.

Reply | Quote

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 for instructions.

Reply | Quote

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.

Reply | Quote

Yep, that’s what I suspected. Glad you were able to solve it yourself!

Reply | Quote