What causes relationships to break and Key fields to be lost?

[Steve in MN]

I have a program that is distributed to 25 of our centers around the country.
The program a survey tracking program with lots of reports and the main
tables are a survey header table and a survey question table.
The program is buttoned up and has the coding in it to not allow the shift
key bypass etc. I also have a user tracking log in it so I can tell if
someone got in the back door somehow.
The db is a traditional front end linked to the back end tables (both are
buttoned up tight) and it is in 2002 all running on the XP professional
desktops.

The problem that has happened at the same buidling 5 times in the last 2
months is that the relationship is lost between the survey header and the
survey questions tables and also the key locks that are on the survey
questions table (record # and Question #) to prevent duplicates are also
removed. The way it is found out is that duplicates show up on the survey
entry form.

Question is what can cause this to happen and what can I do as a programmer
to prevent it? I should note that there is one front end for the data entry
and another that is used for the reporting...2 copies linked to one back end.

None of the other buildings are having this problem with identical programs.

 

[Allen Browne]

The most likely problem is a corruption.

When you perform a compact/repair, Access rebuilds the indexes. If it
discovers that there is a duplicate in a unique index, the repair could
solve the problem by discarding data, or by discarding the index. Naturally,
Microsoft chose to discard the index rather than to discard the data.

If another table has a foreign key field that relates back to this field
with referential integrity turned on, the loss of the index also results in
the loss of the relationship. This sounds like what you are seeing.

When JET makes these changes, it creates a new table with "Compact errors"
in its name. You can examine the creation date of this table to discover
when compact/repair discarded the index and relation(s.)

In terms of prevention, it's the standard stuff you do to prevent
corruption. Here's a list of things I've found useful:
http://allenbrowne.com/ser-47.html

This problem is not common if the machines, network, power, and users are
all working reliably. If it is recurring, it would suggest that something is
causing interrupted writes, and your goal will be to identify what that is.

HTH