Access shuts down after opening query

[MarkusJohn]

Hi,

I'm sort of lost with a big access DB, that contains large amount of
tables and complex queries.

Yesterday I was able to amend a query in design view, but when I tried
to run it Access has shut down.
Today I tried the same and I'm not even able to open the query in
design mode any more.

Compact and Repair I have tried but this does not help anything.

Does anybody knows what to do in such a case?

Thanks,
Markus

 

[Allen Browne]

Markus, there could be any number of reasons triggering this crash.

To try to rescue the query:
1. Open the Immediate Window (Ctrl+G) and enter:
? CurrentDb.QueryDefs("Query1").SQL
substituting your query name for Query1.
If it prints the query's SQL statement, copy it to Notepad, and save.
Then delete the query.

2. Make sure Name AutoCorrect is turned off. For how and why, see:
Failures caused by Name Auto-Correct
at:
http://allenbrowne.com/bug-03.html

3. Compact the database.

4. If the query (or any queries that it relies on) uses any VBA functions --
whether user-defined ones built-in ones like IsNull(), Left() -- decompile,
compact again, set minimal references, and compile. Details in steps 4 - 8
of this Recovery sequence at:
http://allenbrowne.com/recover.html

5. If the query uses any other queries as input 'tables', open those and
make sure there is nothing in their Filter or Order By properties. Continue
down the line until you are drawing from tables only. Then compact again.

6. Create a new query. Cancel the dialogs. Switch to SQL View. Paste in the
query statement you saved back at step 1. Switch to design view. Save.

If you are still stuck at this point, the problem is deeper, such as a
yes/no field that contains a Null (e.g. in a subquery or on the outer side
of a join.) Explanation:
http://allenbrowne.com/NoYesNo.html

Or, it could have to do with badly matched data types.