The autonumber key in a table of my application is duplicating. Full records are duplicated in the table sharing the same autonumber while other newly inserted records (through a form) simply contain a duplicated autonumber and the other fields in the record are not duplicated. I have read about this issue occuring with 2000, but not Access 97 and I'm wondering if this problem has been encountered before.
This behaviour is inconsistent and I cannot seem to replicate it throughout various tests through my forms.
I have compacted and repaired the database. Would this have any repercusions on my data? Also, I have noticed strange behaviour with this particular table. When I open the table and then try to sort on the autonumber key duplicate records are produced. As well, when I perform a straightforward query on this table, other records are being retrieved and shouldn't be.
I am thinking that the entire database has been corrupted. What can cause the creation of duplicate autonumbers? Is this a deficiency with Access or a potential bug in my application?
Any advice or feedback on this problem would be greatly appreciated.