Unanswered: Weird: Impossible Duplicate Key / Possible Sharing Problem
I have a db that is shared on the network. Various users update a core table via a form.
This core table has a unique ID field (data type = number) that is the primary key and is set to indexed - no duplicates.
What's happened (and I don't know how) is that one record, say record 100, has entirely another records details (say record 200). I know this because when I sort the ID field, record 200 is where 100 should be (!) and yes, record 200 is still where record 200 should be so there are two of them. If I do a select filter on 200, only 1 record appears so it seems that the table thinks that the record in field 100 is record 100 even though it's showing details from record 200... confused yet?!
So my questions are:
1) any ideas how this happened
2) if it is related to sharing what should I be doing to my db to prevent bad data like this happening again.
I should mention also that we have had a lot of other weird stuff happening on this db: things like the first records data constantly changing with no obvious reason.