Access XP periodically crashing on requery - timing issue

[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 in the vain hope that this might
accomplish something. 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 20, 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 timing of performing the queries and then attempting the
"requery" before the queries are finished. 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, but I am certainly open to any suggestions.

Based upon some others' helpful suggestions, I have tried the
following steps without success (i.e., I still get the same crash
every 10-20 attempts or so):

1. Creating a new .mdb and importing all of the objects
2. Inserting DoEvents before the Requery (as well as after each
individual query)
3. Changing all query calls from Docmd.OpenQuery to CurrentDb.Execute
4. Inserting DBEngine.Idle dbRefreshCache before the Requery
5. Un-binding the forms, running the queries, then re-binding the
forms before the Requery

None of these actions seems to have any effect that I can tell. It
would be easier to debug if it crashed every time, but it doesn't.

Thanks in advance for any help.

[Wayne Gillespie]

On 14 Jan 2004 14:54:01 -0800, pb******@yahoo.com (pbbriggs) wrote:

Does the recordsource of your form (or subforms) include a join to a table used to lookup related data for display only on your form?
I have had 3 "shutdown" problems with A2K2 which were all caused by the use of a join to a lookup table. All were fixed by removing the lookup table
and replacing it with a direct DLookup / TLookup to the table from the display control on the form.

Further info in -
http://www.google.com/groups?hl=en&l...4ax.com&rnum=3

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 in the vain hope that this might
accomplish something. 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 20, 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 timing of performing the queries and then attempting the
"requery" before the queries are finished. 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, but I am certainly open to any suggestions.

Based upon some others' helpful suggestions, I have tried the
following steps without success (i.e., I still get the same crash
every 10-20 attempts or so):

1. Creating a new .mdb and importing all of the objects
2. Inserting DoEvents before the Requery (as well as after each
individual query)
3. Changing all query calls from Docmd.OpenQuery to CurrentDb.Execute
4. Inserting DBEngine.Idle dbRefreshCache before the Requery
5. Un-binding the forms, running the queries, then re-binding the
forms before the Requery

None of these actions seems to have any effect that I can tell. It
would be easier to debug if it crashed every time, but it doesn't.

Thanks in advance for any help.

Wayne Gillespie
Gosford NSW Australia

[pbbriggs]

Wayne,
Thank you for the reply and the link to the other thread. My two
subforms do indeed have recordsources which are queries (and both
queries contain joins). Once I am able to get back to work on my app
(tomorrow), I will see if I can do the same think using Dlookup and
getting rid of the joins. Based upon your detailed post in the other
thread, it sounds like I may be experiencing the same thing that
happened to you. To be honest, I did suspect it was something
peculiar to A2K2 because I started developing the app in A2K and never
had this problem. However, I had made so many other changes since
that time, there was no real way for me to tell for certain if it was
a version-specific problem.
I will post back once I've had a chance to implement your suggestion.
Thank you.
Patricia

Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in message news:<9s********************************@4ax.com>. ..

On 14 Jan 2004 14:54:01 -0800, pb******@yahoo.com (pbbriggs) wrote:

Does the recordsource of your form (or subforms) include a join to a table used to lookup related data for display only on your form?
I have had 3 "shutdown" problems with A2K2 which were all caused by the use of a join to a lookup table. All were fixed by removing the lookup table
and replacing it with a direct DLookup / TLookup to the table from the display control on the form.

Further info in -
http://www.google.com/groups?hl=en&l...4ax.com&rnum=3

[pbbriggs]

Well, no luck. I finally got to implement Wayne's suggestion. My
forms' recordsources now are without joins. Unfortunately, I'm still
encountering the problem. To top it all off, now I am occasionally
getting a Microsoft "Runtime Error! This application has requested
the Runtime to terminate in an unusual way" error.

If anyone has any other ideas, I'd greatly appreciate it.
Thanks
Patricia

pb******@yahoo.com (pbbriggs) wrote in message news:<98**************************@posting.google. com>...

Wayne,
Thank you for the reply and the link to the other thread. My two
subforms do indeed have recordsources which are queries (and both
queries contain joins). Once I am able to get back to work on my app
(tomorrow), I will see if I can do the same think using Dlookup and
getting rid of the joins. Based upon your detailed post in the other
thread, it sounds like I may be experiencing the same thing that
happened to you. To be honest, I did suspect it was something
peculiar to A2K2 because I started developing the app in A2K and never
had this problem. However, I had made so many other changes since
that time, there was no real way for me to tell for certain if it was
a version-specific problem.
I will post back once I've had a chance to implement your suggestion.
Thank you.
Patricia

Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in message news:<9s********************************@4ax.com>. ..

On 14 Jan 2004 14:54:01 -0800, pb******@yahoo.com (pbbriggs) wrote:

Does the recordsource of your form (or subforms) include a join to a table used to lookup related data for display only on your form?
I have had 3 "shutdown" problems with A2K2 which were all caused by the use of a join to a lookup table. All were fixed by removing the lookup table
and replacing it with a direct DLookup / TLookup to the table from the display control on the form.

Further info in -
http://www.google.com/groups?hl=en&l...4ax.com&rnum=3