Help! Critical Access2000 error

[Rick]

An Access2000 database which has been running uneventfully
for several months suddenly crashes several times a day.

Besides "Out of memory" reports the following message
appears occasionally:
"Microsoft Access has encountered a problem and needs to
close. We are sorry for the inconvenience."

....after which the user is booted out.

Several hours have been spent attemtping to identify the
problem with little success. It appears to relate to a
form which has several subforms on it. The error occurs
most when a user is scrolling through records using the
navigation bar. However; the links have been checked
along with everything else and everything appears to set
up correctly.

The only clue is occasionally a parmeter box appears while
the user is scrolling through records asking for a field
which serves as part of one of the links between the form
and a subform. Referential integrity is set on the DB and
queries have been run to attempt to locate a record
without a matching record in the one side of the one to
many relationship. Not have been located.

The Error Report has been sent to Microsoft several times
without any response.

Anyone have any idea what may be creating the problem or
how one might be able to trace its origin?

 

[Allen Browne]

Hi Rick.

These can be very frustrating and difficult to trace.

Let's start by eliminating the obvious.

1. Do you have Serivce Pack 3? Help | About should show Access 2000 SP-3.

2. Do you have Jet 4 SP8?
Find the file msjet40.dll (typically in windows\system32).
Right-click it and choose Version.
Anything less than 4.0.8015.0, you need an upgrade.
Either of these service packs can be downloaded from support.microsoft.com.
If multiple computers, check each one.
This IS important: JET 4 SP8 is known to solve some issues where references
go out of scope.

3. Uncheck the Name AutoCorrect boxes under Tools | Options | General. If
you want to know more about the bugs caused by this feature, see:
http://allenbrowne.com/bug-03.html

4. Compact: Tools | Database Utilities | Compact.

5. From any code window, remove any references you do not need (References
on the Tools menu), and chech that it still compiles (Compile on Debug
menu). More info on references:
http://allenbrowne.com/ser-38.html

6. Is the database shared on a network?
If so, is it split (front end on each workstation, back end shared)?
Constant corruptions are guaranteed if you are modifying the objects in a
single, shared mdb in use by others.


Now down to specifics:
7. Is there any code in the Current event of the form or its subforms? Try
eliminating it.

8. By "navigation bar", are you referring to the navigation buttons at the
bottom of the main form? one of the subforms? or a custom nav bar (i.e.
running code). Any code here would be suspect.

9. MasterLinkFields/ChildLinkFields
Start with the one nominated in the parameter box.
What is the name of the requested parameter?
Is this the name of a field in the main form's RecordSource? A text box on
the main form? A field or control in the subform?
If a field, a calculated field in a query, or a stored field from a table?
What type of field?
Is there anything else that could have the same name (e.g. Section, Name,
Date, Caption, ...)?

10. Source of subforms
Is the RecordSource of the subform a table? Query? SQL statement? Any
possible ambiguity here (e.g. a primary key and a foreign key that both have
the same name in the query)?

11. Calculated controls
Any calculated text boxes on the main form that refer to controls within the
subform? If so, try temporarily removing these.

If none of that helps, begin deleting subforms from the form (compacting
after each delete) until the app becomes stable. This will at least help you
pin down which one is giving the problem.

 

[Rick]

Hi Allen:

Thank you so much for your most informative response. I
checked each item carefully and your #9 was just what was
needed. You comments focused my attention to an error in
the design of a form with a subform which itself
contained a subform.

Problem corrected with your assistance. Thank you.

Rick