Periodic crashing on requery 1

[pbbriggs]

I will try to be as descriptive as possible, but this error has me stumped and I am not sure what relevant info to include....

I am running Access XP on a Windows XP machine. I initially began developing an app in Access 2000 but converted it several months ago to XP. So the app is now Access XP format also.

My app is crashing on a regular basis, with the totally non-descript Microsoft error (Microsoft Access has encountered a problem and needs to close. Do you want to send an error report to Microsoft?). Every time, I send the error report to MS. One time I actually received a response from them, telling me that they were aware of a bug in Access XP that might be causing my error, and that the bug would be fixed in Office XP Service Pack 3 (which still has not been released).

Anyway, I'm not entirely convinced that it's a MS error because it does not happen with regularity on the same event(s). The error nearly always occurs on one particular form, when I click a button that performs a series of queries based upon some "If Then" conditions. After performing the queries, the main form (on which the button is located) also performs a requery of two subforms (which are continuous forms). All of this works fine (the queries, the If conditions, and the two Requery events), so I do not believe I have a code issue. However, about one time in every 10 or so, I click the button, the queries will run, and then (I think as it is attempting the requery), I get the error and Access shuts down. What I have noticed is that if I temporarily move the back-end off our server and onto a local PC (where the front-end is housed), the error occurs much less frequently (maybe once in every 50 tries instead of once in every 10), which makes me think it may have something to do with the BE sitting on a network drive. Otherwise, though, the FE-BE setup seems to work fine (no real speed issues or anything). Also, I am the only one using the app right now, so it's not an issue of multiple users trying to access it at the same time or anything like that.

Anyhow, I know there may be no one that can help based upon this amount of information. I guess I am just wondering if anyone has had a similar error and if anyone knows if there may be some network/refreshing type settings I should check on, or anything like that. If it crashed every time, I would obviously assume a problem with my code, but the fact that it runs fine more times than not has me stumped.

Sorry for the long-winded description. If anyone has any ideas (or even stabs in the dark), I'd certainly appreciate hearing them. I am trying to roll this app out for other users in the near future, and obviously I can't have it crashing all the time.

Thanks.

 

[psemianonymous]

1. Very good description. I'm long-winded too, and that's a good thing. At least here.

2. I can't help you directly. I've had some instability issues, usually when I did requerying and such on an Event() subroutine of some sort, and I think this is sort of a timing issue. My suggestion for you is to try and "linearize" the process so that only one thing is happening at any given time--basically ensure that your continuous forms don't try to requery when the DELETE/UPDATE/INSERT/"WHATEVER" queries are still running. You could do this by totally un-binding the subforms, or maybe even unloading the main form entirely, then run the queries, then reload the (sub)form(s).
But that's a lot of work to do, on someone else's hunch.

If you think it's a corruption issue (the 2000->XP conversion weirdness), create a new database and import everything into the new MDB--that usually fixes any "mild" corruption, or fails miserably halfway through. But I don't think it's a corruption issue.

If you want, repost your question on comp.databases.ms-access, accessible via Google Groups. I won't mind. There's a lot more advanced help there. So if no one else replies to this, try there. I have a link to the Google Groups page below.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[pbbriggs]

Thanks for the response Foolio...

I did forget to mention, I did attempt creating a new db and importing everything. I also regularly compact and repair the BE. I'm pretty sure (even with this frequent crashing) that I don't have any corruption (yet... knock on wood).

I think you are dead-on with the timing issue theory. I have always had the sense when it crashed that somehow the preceding queries were still attempting to "do their thing" when the requery started. This is always going to cause a problem because the queries delete records out of the subform(s). A requery while this is still underway is clearly going to be problematic.

Do you by chance know if there's a simple way to sort of put a "break" (not a debugging breakpoint) into the code after the queries, to give them time to finish before the requery starts?

In the meantime, I am going to look into your "unload" suggestion to see if it can be done without the user even noticing. I'll post back when I've figured it out.

Thanks again for the reply.

 

[psemianonymous]

You can do delays[3], but there's no guarantee that the query finished before the delay finishes. I think there's a few things:

-Don't use DoCmd.OpenQuery. I like to use it because it automatically puts the progress bar at the bottom. But if you want to guarantee it finishes, use
CurrentDB.Execute "qryName", dbFailOnError
instead[1].

-Really there's no way to know for sure, that I've seen. The help (so far as I've seen, Access 97) doesn't mention which commands spawn new threads or which are executed in-line[2]. With the exception of OpenQuery/OpenForm/OpenReport, for which I'm 90% sure they don't have to finish before moving on.


Bah. I'm hoping someone else chimes in with (hopefully) more experience in the matter.


Pete


[1] - I'm not 100% that db.Execute() will wait until the query is finished before moving to the next line.
[2] - 'threads' used in the loosest manner possible--meaning 'anything that doesn't have to complete before moving onto the next line' in this context

[3] - delay code I made up yesterday:

Public Sub WaitSeconds(Seconds As Integer)
    If Seconds > 60 Or Seconds < 0 Then Exit Sub
    
    Dim datStart As Date
    
    datStart = Now()
    
    Do While DateDiff(&quot;s&quot;, datStart, Now()) < Seconds
        DoEvents
    Loop
    
End Sub

Delay code as posted on the MVPs site:

http://www.mvps.org/access/api/api0021.htm

 

[pbbriggs]

Pete,

Thanks again for your persistence and suggestions!! I want to give you a star for all the suggestions, but first I will figure out which one winds up being the solution (to make it more useful to future readers).

I tried changing over all my queries from DoCmd.OpenQuery (which was the syntax I had used for all of them) to CurrentDB.Execute. I got excited because it seemed as though it may have fixed the problem. Unfortunately, I kept working and did get the crash.

I'm going to stick with the theory that it's a timing issue and try some un-binding and/or unloading of the forms before the requery. I'll post back with results.

Thanks again
Patricia

 

[pbbriggs]

Pete,

Would this be what you would suggest to "un-bind" the subforms while the queries are running?

1. Set the recordsource of the subforms to =""
2. Run the queries which will update the data underlying the subforms
3. Set the recordsources back to the appropriate tables/queries
4. From the main form, run the requeries of both subforms

???

I tried the above method. It seemed to work for a while but eventually I got a crash again. It still seems to be doing fine through the query stage.

I am hesitant to try your "delay" routine because these queries are actually quite speedy and I feel like that will unncessarily slow down the app.

Thanks again for all of the help and suggestions. Maybe I will try the other forum you suggested also.

 

[psemianonymous]

Unfortunately, those are exactly what I meant. Hmm, so it didn't work.

I don't recommend doing any sort of delay.

I just searched the newsgroup and turned up one interesting thread: did you have the "OrderOn" set on either of the subforms? This person experienced crashes when Requery'ing the subforms, WHEN they had ordered the subforms in some manner. Check the thread:

http://groups.google.com/groups?hl=...m+crash&meta=group%3Dcomp.databases.ms-access

Yeah, I'm pretty much out of suggestions. Sorry I couldn't be of help.


Pete

 

[pbbriggs]

Thanks for the link (and for all of your effort on my behalf). Unfortunately, not only are my subforms sorted, but they allow active re-sorting (by clicking on the headers of the columns). I will try the OrderBy off/on trick but if it affects the sorting the user has applied, that will be a problem.

I am going to keep trying. In the meantime, I'm giving you a star because I think the CurrentDB.Execute is a good tip for those users like me who learned by mimicking MS's way of doing things (DoCmd.OpenQuery) which are not always the best way. I also know you are on the right track with the timing stuff. Now that I have made improvements (using "execute" and unbinding/rebinding the subforms), I am thinking maybe it is some kind of MS bug after all. I will be eagerly awaiting Service Pack 3 to see if that helps.

Thanks again for all of your suggetions and patience.

 

[pbbriggs]

In case anyone is reading this, I finally figured out what was causing my crash and posted it in Thread 702-772398. Thank you to foolio12 for helping me improve my code even though my crash was unrelated.

Patricia