Change/Save to aggregate query shuts down Access

[BillC]

I'm working with a query (written by another) that nests 3 IIF's into a
single expression, then performs a GROUP BY on about 12 fields, including
this expression. The query runs fine, and modifications can be made and run
in SQL or Design view. However, when I modify the query and attempt to save
it, Access shuts down and changes are not saved. Pasting the query text into
a new query, changing it, and saving it has the same result. Even the
simplest change causes this.

The answer I think is to break down the query into multiple simpler queries,
but does anyone understand this behavior?

 

[Golfinray]

It could be eating up all your memory? How much memory do you have? I have
some large queries to take a while to run and the reports off of those will
lock me up sometimes.

 

[Jerry Whittle]

Are you calling on any functions? That could be the problem.

I'm thinking that the database file is corrupt. Open a new database file and
import everything from the problem database into it. If something won't
import, it was corrupt.


Tony Toews has an excellent web page on database corruption.
http://www.granite.ab.ca/access/corruptmdbs.htm

Allen Brown also has excellent info on corruption.
http://allenbrowne.com/ser-47.html

I have a white paper in a Word document named Fix Corrupt Access Database
towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

 

[BillC]

Your idea makes sense given the behavior, but I have 2 GB on my laptop, and
even with lots of stuff open there's still 1.2 GB free.

 

[BillC]

There are a few date functions called, but they seem innocent enough. I
neglected to mention that the tables are linked SQL Server tables- does this
make corruption quite a bit less likely?

I think I'll have the user create a new database, link to the tables needed
for this query, and try again. If it works, then re-link the other tables
and recreate the other queries. Sort of the equivalent of importing all the
data into a new database?

Thanks for your help!
BillC

 

[Terry]

Go to event viewer and find out why Access crashed. If it says something
like access has caused and error in Nt.dll then there is a kb article about
it and a download to fix it. Access 2003 only.

 

[Alan]

I'm working with a query (written by another) that nests 3 IIF's into a
single expression, then performs a GROUP BY on about 12 fields, including
this expression.  The query runs fine, and modifications can be made andrun
in SQL or Design view.  However, when I modify the query and attempt to save
it,Accessshuts down and changes are not saved.  Pasting the query text into
a new query, changing it, and saving it has the same result. Even the
simplest change causes this.

The answer I think is to break down the query into multiple simpler queries,
but does anyone understand this behavior?

Hi,

If you still cannot repair your Access database, I think you can try a
utility called Advanced Access Repair to repair your Access MDB file.
It works rather well for my corrupt Access MDB files. Its web address
is http://www.datanumen.com/aar/

Alan