Re: Help! Union Query has started crashing!

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.

"Andy C Matthews" wrote:

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

"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Andy C Matthews" <AndyCMatthews@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:8ECD910D-805A-4D9D-9A31-54557B0398D4@xxxxxxxxxxxxxxxx
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



.

Relevant Pages

  • Re: access 2003
    ... I removed the parameters from the form query source. ... boxes from the form header, events, code, etc and ran the form query source ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would be ...
    (microsoft.public.access.conversion)
  • Re: access 2003
    ... I removed the parameters from the form query source. ... synchronize combo boxes to the detail section or the parameter form query ... forms queries and the SQL because syntax of the SQL will change randomly. ... the Access 97 database, I wouldn't have thought any expressions would ...
    (microsoft.public.access.conversion)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... The list in each case has to have the same number & types of fields (for example, if the first Query begins with a Date/Time field, the second one should do so as well). ... I like to keep my Union Queries short and simple and do the rest of the work elsewhere. ... tblMentors comprises Subject Mentors and Professional Mentors and the Placement subform has a combo for each - the Subject Mentor combo puts the chosen MentorID in the SubjectMentorID field and the Professional Mentor combo puts the chosen MentorID in the ProfessionalMentorID field. ...
    (microsoft.public.access.queries)
  • Re: Query In BE database
    ... that would give the users "live" access to the underlying queries. ... When selected from the drop-down list box, I what the the query that is ... What Is A Remote Query? ... point your local query to a query that was in another Access database. ...
    (microsoft.public.access.queries)
  • Re: Combining records from two queries
    ... combining UNION and SELECT queries to best effect. ... Using my query names, can you say exactly what my SQL should be ... > my Union Queries short and simple and do the rest of the work elsewhere. ... >>> Subject and Professional Mentors that are allocated to placements. ...
    (microsoft.public.access.queries)