Duplicate Primary Key (Access 97)

Topic

New Reply

I have a vb application with an Access 97 db backend. There are 3 main tables in the application for Order details, Catalogue information & Order quantity info…

In the catalogue table ‘Order_Detail_Summary’, there are two key fields – Order_Header_Counter and Counter (which is an Autonumber field). A few users have just reported error 3022, duplicate key. Looking at the table…

OHr_Counter Counter
76247 92239
76247 92240
76248 92239
76248 92240
76248 92241
76249 92242
76250 92243
76251 92244

Access has allowed 92239 & 92240 to be created twice in an auto number unique field?! How can this happen, surely Access should kick up an error…?

Thanks

Reply | Quote

Replies

Declaring a field as an autonumber doesn’t automatically make it unique. To do that, you must specify that this field is the PrimaryKey or you must create an index for this field and specify it as Unique.

Reply | Quote

The field is unique. That’s the dilemma

Reply | Quote

I’ve never seen a situation before in which Access allowed duplicate values into a field with a unique index. Access2000+ and Jet4 have a problem in which it loses the seed value for the table and will try to add an autonumber value which already exists. If the autonumber field is not unique, then the record gets added; otherwise an error occurs. You might want to double check to make sure that field is indeed unique. Also, is it possible that some users have Access 2000 or later?

Reply | Quote

You might also check to see if you have the latest service pack for the Jet engine – there were a number of them released for Jet 3.5x. But more than likely, you have a situation where the index for that field has been corrupted – are you doing a regular compact and repair somewhere in your application?

Reply | Quote

Yes, the database is compacted when the VB application is first opened & no other users are connected.

We do have version / OS issues in the company. Some of our users are on thin-client Citrix (office 2000), while others are using NT4 with Access 97. Still, the VB app is compiled with Jet 4 and just creates a DAO.Database object to communicate with the database, version incompatibilites shouldn’t really come into it…

Maybe I’ll recreate the index, do & repair & compact…….then just hope it doesn’t happen again…!!

Thanks for responses…

Reply | Quote

If in fact you do have duplicate autonumber values, it would seem to me that you would get an error on trying to compact.

Reply | Quote

If you are using Jet 4 with an Access 97 back-end, you may well have problems – you post suggests you compiled it with Jet 4. We have seen serious corruption when early version of Jet 4 were being used against an Access 97 back-end. I would either be sure you are working with Jet 3.51, or upgrade your back-end to Access 2000. In any event, be sure and get the latest service pack for what ever version you settle on.

Reply | Quote