Corrupt record/index/relationship?

[aamouse]

From time to time, since migrating to Access 2003 a year or so ago, we have
noticed what appears to be a corrupted record in one table or another. E.g.,
unprintable characters appear at the beginning of the record, text data is
moved to (and often split between) other fields, qty fields contain
astoundingly huge numbers, etc.

Interestingly, we have most often found these records when querying, for
instance, for data for a particular client; the query output will include the
"bad" record, even though the client name as it now appears in the "bad"
record doesn't match the query criteria (though it did originally). Does
anyone have a clue what would cause such strange records, in which the data
is out of synch with the index?

The latest corrupt record was discovered in a particular parent table. We
subsequently discovered that the relationship between that table and its main
child table was MISSING. Was this relationship removed by the Compact/Repair
we performed on the database, when it found it had records on the "many" side
that no longer had a match on the "one" side, hence violating referential
integrity? Or is it more likely that whatever caused the corrupted record is
also responsible for removing the relationship?

 

[John W. Vinson/MVP]

From time to time, since migrating to Access 2003 a year or so ago, we
have
noticed what appears to be a corrupted record in one table or another.

See the suggestions at Tony Toews' corruption FAQ:

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

There are many causes of corruption; from what you describe, you may have
one or more corrupt records in a table, and those are corrupting that
table's indexes (and therefore relationships). You may be able to repair the
damage using Tools... Database Utilities... Compact and Repair, but more
likely you'll need to create a new, empty database; import everything that's
*not* corrupt; create a new, empty table matching your corrupt one; and run
an Append query to move all the records EXCEPT the damaged one into the new
table.

Good luck. MAKE BACKUPS FIRST!!!

 

[aamouse]

John,
Thanks for your advice. I found the corruption info from the links at Tony
Toews' corruption FAQs site to be very enlightening.