Access unexpectedly closes when saving a query...

[belzar]

Anyone have any idea why access would unexpectedly close down while trying to save a query? The query runs fine. If I do something as simple as moving a table in the layout view and try to save the query, Access shuts down completely without saving.

Thanks
This message was edited by belzar on 2002-12-06 09:25

 

[Corticus]

Hi,

Well, that doesn't seem right! Since you don't seem to be getting a whole lot of responses, I'll suggest a couple of things.
First, try compact and repair, under tools|database utilities (make sure to save a backup first!)
Second, try exporting the tables and queries to a new database (right-click on each table/query and select export).
Third, get a shotgun, take your computer out back, and teach it who's really boss!

HTH,
_________________<EMBED width="118" height="52" src="http://corticus.biz.ly/CorticusLink.swf"></EMBED>
This message was edited by Corticus on 2002-12-06 16:39

 

[belzar]

Thanks Corticus, I've tried everything I can imagine... I've exported everything to a new database, I've made a new database and imported everything in, I've renamed tables and queries, I've compacted and compared, I converted the database from 2000 to 2002 and I even tried tempting the database into submission with reboots and upgraded RAM... All with no success... I have a current copy of the database residing on a server while I make updates to a local copy of the database. At first I thought the problem started after making changes to a particular query. However, I was able to recreate the changes on a fresh copy of the database with no problems. Now I suspect the problem starts when I update the new database with data from the copy on the server. I've tried importing, exporting and even copy/pasting the data but each time the problem occurs. Once the problem starts, I can't find any way to correct it other than going back to an older copy or making a new copy. I'm just plain flabergasted at this point.

Robert Hall

P.S. I've also noticed that the problem only affects certain queries which, so far, appear to link back to the query I suspected to be the cause originally but could not verify.
This message was edited by belzar on 2002-12-09 13:11

 

[Corticus]

Yuck...

Now that I think about it, I have had a db do something similar to this. I had a query that I couldn't touch, or Access would freeze or shut down. The problem with my db was that the query had some logical error in it that was confusing Access to the point that it just gave up and died. I can't see how data itself would cause a db problems, except that maybe a query is expecting data in some format, and the query doesn't get all messed up until you make it try to deal with data that's not in that format.
I guess you'll have to go with my third suggestion...

good luck,
Corticus

 

[belzar]

I have to say that it does appear that the problem changes. At first you can open the query but you can't save any changes. Later you can't even open the query. Access shuts down as soon as you try to open it. I wonder if it's a combination problem. Maybe it's a logical error like you said and the bad data is getting moved in later. The thing that puzzles me with that is that, each time I have this problem, I start with a fresh copy of the current database in production which contains the data that I had just moved over and possibly caused the problem. However, I am able to make my query changes again with that data and it works fine until I go and update the data again. Unfortunately I can't take the production copy offline long enough to make the changes I need. I'd like to go with your third option but I fear that my superiors will go with option three on me...
This message was edited by belzar on 2002-12-09 15:45

 

[belzar]

Well, I think I found the reason... One of my changes to the query was to add a field based on IIF statement that went 10 deep. Even tho' this worked it seems to be unstable... working sometimes and causing access to close at other times. I came up with another way to do the field and so far have had no problems... We'll see...

 

[Corticus]

Yes,

That is similar to the query that gave me grief. I wanted to create a select query with lots of different criteria, with the option of each criteria being null. I was new to Access, so I made a query of a query of a query and so forth, until I had one query for each criteria(about 15). Needless to say, Access did not like my 15 query chain, and gave me endless grief, often in the form of freezing up, or kicking me out of Access altogether.

I think Access tries too hard to make sense of things that aren't ever really going to make sense.

Glad you got it figured out!
Corticus