Help! Union Query has started crashing!

[Guest]

I hope someone can help with this!
I've got a union query, qryCombinedData, which combines the contents of
queries queryforMainFormReadingRita and queryforMainFormInfoterra. These
queries, in turn, draw on linked tables (as the data is stored in a seperate
Access DB that many users contribute to throughout the day). It has been
working fine for weeks, and now Access crashes when I try and run the Union
query! I can open each of the Queries the Union Query draws on separatley
fine.
Is this something a compact and repair (on the data store database) will
solve?
Please help!!
Andy, UK

 

[Allen Browne]

Suggestions:
1. Make sure Name AutoCorrect is off on both the front end and the back end.
Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Then compact the database to get rid of this junk:
Tools | Database Utilities | Compact
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Does it work if you use UNION ALL instead of UNION?

3. Nulls in Yes/No
Are there any yes/no fields in either query?

4. Calculated fields.
Are there any calculated fields or literal values, where Access could
misunderstand the data types?

Since the UNION cominbes 2 queries which themselves read other tables or
queries, it is probably too big a task to debug this for you here. But
basically you need to track down where the data types could be
misunderstood.

 

[Guest]

Allen,
Thanks for the prompt and informative help - it's great that there are
people like you out there that try and help with others' problems.

I think I have tracked the problem down to the query
queryforMainFormInfoterra, which is one of the union-ed queries. This draws
on linked tables tblMainData and tblSupport in the back-end database. I
believe tblMainData is the source of the problem, as it crashes when you try
to perform any complex operation on it (a new query, or copying the data to
the clipboard). I can't find any Null values in the Yes/No fields of the
query as it says 'Record is Deleted' when I try to run it. I have switched
off the AutoCorrect on front and back ends. I can't run a Compact & Repair on
the back end now as we have about 50 people editing the data in it during
office hours - so it will have to be done at night.
I also tried a UNION ALL, to no avail. I don't think there is a problem with
the actual structure of the queries as they have been running fine for weeks.
I really hope the Compact & Repair on the backend will solve it!

Many thanks
Andy

 

[Guest]

Ok. I have tracked the problem down to one of the back-end databases (to
which tables are linked in the front end and the crashing Union query runs
on). It's tblSupport on the RLR_SUPPORT_INFOTERRA.mdb database. This table
seems to be corrupt. When I try a compact & repair on the DB, it crashes. If
I try to select all -> copy the data in this table, it crashes. If I try an
export, it crashes. There is a record with #DELETED in each field, that I
delete every time but always comes back. I really can't figure out a way to
clean up this table, help!
Thanks again.

 

[John Spencer]

Three ideas

Try creating a new database and importing all the data

Try creating a new table and importing records from the bad table using
criteria.

Try creating a new table and copying groups of records and pasting them into
the new table.

 

[Allen Browne]

To get John's idea to work you may need to create a query that does *not*
ask for the row where the #Deleted is.

Details in the 3rd symptom in:
Recovering from corruption
at:
http://allenbrowne.com/ser-47.html