Duplicate records

[ThrillKillKult]

I am working with Visual Basic .NET and am programming using a Microsoft Access 2000 database. I am running into a problem with duplicate records.

At some points in time (randomly as far as I can discern), when VB attempts to create a new record, it gets a duplicate of the last primary key in the table instead of incrementing. The primary key is never touched in the code - it is not changed, accessed, or edited in any way. It is simply an autonumbered field that is left to do its own work.

The only way that I can fix this problem is to create a new database and copy all of the tables (except for the corrupted one) to it, then create a new table and copy data from the corrupted one. This, of course, is a hassle. I was wondering if anyone had any experience with this type of thing before, and if there was anything that I could do to resolve this problem (or if there was any insight into what is causing it), or if it is a genuine Access 2000 bug and I need to get an upgrade or a new database system.

Thanks.

 

[RoyVidar]

It could be a bug, ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database or for 2003, with a workaround BUG: You may receive an error message when you try to insert a new record in a table that contains an Autonumber field in Access 2003 - note - I think for the workaround to work, that the autonumber in question cannot be involved in relationship with other tables.

Else, another guess, it might be the same reason that made MichaelRed write this faq faq700-184. Though the code there wouldn't work in .Net, the method could probably serve as a starting point, should this be the reason.

Or, there may be other reasons, too. I'll bet, if you post in the VB.Net forum (forum796), you'd be advised to use MSDE/SQL-server;-)

Roy-Vidar