Saving Query shuts Access down...???

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am seeing my application shut down (although a *.ldb file is still listed)
when I try to save a query!

I am working in Access 2002, but over the weekend sent my db home to work
there (where I have Access 2003).

Can anyone please let me know why this is happening?

Thanks,
M
 
It's almost 100% sure that your database is corrupt. The type of corruption
that you are seeing probably can not be fixed with a Compact and Repair or
even using the JetComp tool.

Open a new database and import every object from you problem database. Save
the bad query until last. Don't be surprised if it won't import. If it does,
and causes the same problem again, you will need to rebuild that query from
scratch.

If nothing else works, hopefully you have a recent backup.

If you are using a portable media like a flash drive or even floppy disk,
make sure to close both the database and Access before removing the media. My
students corrupt databases all the time by yanking out the floppy or flash
drive then shutting down the database.
 
Jerry said:
It's almost 100% sure that your database is corrupt. The type of corruption
that you are seeing probably can not be fixed with a Compact and Repair or
even using the JetComp tool.

Open a new database and import every object from you problem database. Save
the bad query until last. Don't be surprised if it won't import. If it does,
and causes the same problem again, you will need to rebuild that query from
scratch.

If nothing else works, hopefully you have a recent backup.

If you are using a portable media like a flash drive or even floppy disk,
make sure to close both the database and Access before removing the media. My
students corrupt databases all the time by yanking out the floppy or flash
drive then shutting down the database.
Click to expand...

To add to what Jerry said. If the problem query won't import into the
new DB you can copy the SQL from that query and create a new query in
the new DB w/ the old query's SQL. If you can't open the problem query
in SQL view in the old DB you can get the SQL like this:

Open the Debug window (Ctrl-G) in the old DB and type in the following:

? currentdb.querydefs("the query name").sql

Cut & paste the resulting SQL string into the new query.
 
Jerry's answer is the most likely; however, I've seen two other scenarios
cause this also.

1) Jet and/or MDAC patches not up to date.
2) While this shouldn't matter, sometimes it does. Remove all UNNEEDED
brackets and parentheses from the query while in SQL view and try to save
the query. By unneeded, for the brackets you can probably remove them from
around any table or field name that does NOT have a space in it; for the
parentheses, Access insists on putting multiple layers of parentheses around
each part of the WHERE clause. Use only those that are needed to maintain
the desired logic.

If none of the above works, try setting the SQL of the query using code. If
that doesn't work, you may at least get a better error message to let you
know what is going on.

Example:
strSQL = "SELECT ..." 'etc
CurrentDb.QueryDefs("MyQuery").SQL = strSQL
 
Wow...any idea why it became corrupted? Is there anything I should be doing
differently?

Also, I just created a simple query and saved it and that was fine...????

The query in qn however, is as follows:

SELECT tblMCTSLocalDropped.[Week Tracker],
IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","") AS DESCRIPTION,
Sum(tblMCTSLocalDropped.actl_elig) AS SumOfactl_elig
FROM tblMCTSLocalDropped
GROUP BY tblMCTSLocalDropped.[Week Tracker],
IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","")
HAVING (((IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","")) Not Like ""));

Sorry if this is too confusing, but I am just wondering if I should have
constructed my IIF statement field differently...

Thanks again!
 
Thanks to both of you for taking the time to help me out.

I have set up a new db as suggested and imported items into it (and have
also realized some inefficiencies and so have managed to make it a little
lighter in the process). Regarding the convoluted query in qn, I rewrote it
in the Expression Builder and was able to run it (and save it) without any
issues...phew!!

I am sure that many of my builds are perhaps chunkier than they could be and
through my learnings from this board I have been able to make great strides
in my knowledge base, so I thank you for helping and for bearing with me.

M
 
Wow. That query sure has a lot of embedded IIf statements and probably is
rather complex for Access to run. You would probably be much better off
either normalizing the data a little better OR creating a Case statement in a
function and running the data through it.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Monish said:
Wow...any idea why it became corrupted? Is there anything I should be doing
differently?

Also, I just created a simple query and saved it and that was fine...????

The query in qn however, is as follows:

SELECT tblMCTSLocalDropped.[Week Tracker],
IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","") AS DESCRIPTION,
Sum(tblMCTSLocalDropped.actl_elig) AS SumOfactl_elig
FROM tblMCTSLocalDropped
GROUP BY tblMCTSLocalDropped.[Week Tracker],
IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","")
HAVING (((IIf([tblMCTSLocalDropped]![c_desc] Like "BRONZE*","BRONZE","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PROJIN*","PROJIN","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "STAR*","STAR","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "PO*","PO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "SK*","SK","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "MC*","MC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NH*","NH","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "KO*","KO","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "NC*","NC","") &
IIf([tblMCTSLocalDropped]![c_desc] Like "FU*","FU","")) Not Like ""));

Sorry if this is too confusing, but I am just wondering if I should have
constructed my IIF statement field differently...

Thanks again!

Jerry Whittle said:
It's almost 100% sure that your database is corrupt. The type of corruption
that you are seeing probably can not be fixed with a Compact and Repair or
even using the JetComp tool.

Open a new database and import every object from you problem database. Save
the bad query until last. Don't be surprised if it won't import. If it does,
and causes the same problem again, you will need to rebuild that query from
scratch.

If nothing else works, hopefully you have a recent backup.

If you are using a portable media like a flash drive or even floppy disk,
make sure to close both the database and Access before removing the media. My
students corrupt databases all the time by yanking out the floppy or flash
drive then shutting down the database.
Click to expand...
Click to expand...
 
Hi,

While preusing Allen Browne's excellent web site ( http://allenbrowne.com )
I saw information about Name AutoCorrect possibly causing the problem. It's a
known issue with both Access 2000 and 2002. The fix is to turn Name
AutoCorrect off which IMHO is a good idea anyway. Read all about it at:
http://support.microsoft.com/Default.aspx?id=319491

The title says "Access quits unexpectedly when you run a query" but the same
print also says it can happen when you save a query.
 
Hi, Jerry, thank you so much for your suggestion; I had the same problem and
tried to correct it in several ways without succes until I saw your posting

Ana Morales
 
Back
Top