Bug in MS Access 2000 and 2003 allows duplicate keys?

[raymartin]

I have two tables that periodically gets duplicate records, but I don't know
how that happens.

TABLE1 has a PRIME KEY, INDEXEX, NO DUPLICATES. It is NOT an Autonumber
field, and the value is programmatically created and checked if it exists
before being used. Each week about 1-3 records a re duplicated. No error
mesages appear, and no .NET Error traps are invoked. How could that happen?

The duplicate values can be seen in table view. The duplicates are not seen
if you run a MS Query. The dups appear if you run a Crystal Report.

If I remove and replace the index the dups go away, without needing to be
manually deleted.

If I run Check and Repair the database then the MS Sysinfo table shows
errors , and the index cannot be applied until the dups are manually deleted.

Any ideas?

 

[John Vinson]

I have two tables that periodically gets duplicate records, but I don't know
how that happens.

TABLE1 has a PRIME KEY, INDEXEX, NO DUPLICATES. It is NOT an Autonumber
field, and the value is programmatically created and checked if it exists
before being used. Each week about 1-3 records a re duplicated. No error
mesages appear, and no .NET Error traps are invoked. How could that happen?

The duplicate values can be seen in table view. The duplicates are not seen
if you run a MS Query. The dups appear if you run a Crystal Report.

If I remove and replace the index the dups go away, without needing to be
manually deleted.

If I run Check and Repair the database then the MS Sysinfo table shows
errors , and the index cannot be applied until the dups are manually deleted.

Any ideas?

It sounds like the indexes in your database may be corrupted beyond
the power of Compact and Repair to fix. See the suggestions at

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

You may need to remove the indexes on this table; create a new, empty
database; Import everything from this database into it; and recreate
the indexes. Also, make sure that Name Autocorrect is turned off and
that you have all the current service packs on both Access and JET.

John W. Vinson[MVP]

 

[raymartin]

Thanks John. I checked the links and I am up to date on patches and hotfixes.
I will rebuild the database and tables as you suggest, and reply with the
result in about 1 week