Can't open query

[scotty22]

Hi there,



I have been using a union query fine, and just randomly it prevents me from opening it...even in design view!!!

If I try to open it as a datasheet, I get the message 'Query input must contain at least one table or query', and if I try to open it in design view I get 'The search key was not found in any record'.

Any ideas?

Kind regards,

Scotty22

[NoellaG]

Hi Scotty,

it's normal you can't open a union query in design view; However it should always open in SQL view. have you tried that?

grNG

[scotty22]

I cannot even open it in SQL view.

[NoellaG]

Hi,

do you know what SQL statement it should contain?
What's the different in your database the times you can open it and the times you can't. Is it possible there are some linked tables/datasources in your database that aren't always available?

grNG

[scotty22]

It's a union query of 15 other queries and selects all columns each time i.e. SELECT * FROM....UNION SELECT * FROM.....etc (and I can open all of these fine).

The only thing I did before I tried opening it, was I changed a string text in one of the excel spreadsheets, which is linked.

Any more thoughts?

[NoellaG]

Hi,

the select * is useful, but can be dangerous.
If in one of the datasources a column is added or removed, or in the Excel file a text was entered in a numeric or date column that's enough to trigger an error.
In an union query all datasources have to have exact the same number of columns, and all data types of all columns should match.

grNG

[scotty22]

Noted. I just find * much easier on the eyes. I still can't open the query though and I've tried compacting and repairing...

[NoellaG]

Hi,

I'm afraid the best you can do is pick the last working version up from a back-up file, or start to rewrite the union query peace by peace. I would start writing the first line select * from X union select * from Y, try it out, if it works add another statement, try it out ect. till you find out the line that gives the eroor.

Good luck
NG

[scotty22]

Just redid it and it works fine. I suppose maybe the query itself got damaged? I don't really know enough about the programming behind Access so I'll leave that one with the experts.

Thanks a bunch