Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Primary Key (Access 97)

    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

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Duplicate Primary Key (Access 97)

    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.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Primary Key (Access 97)

    The field is unique. That's the dilemma

  4. #4
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Duplicate Primary Key (Access 97)

    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?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  5. #5
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Duplicate Primary Key (Access 97)

    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?
    Wendell

  6. #6
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Primary Key (Access 97)

    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...

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Duplicate Primary Key (Access 97)

    If in fact you do have duplicate autonumber values, it would seem to me that you would get an error on trying to compact.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Duplicate Primary Key (Access 97)

    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.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •