Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re-use of autonumber causes violated (2000/XP)

    I'm trying to add a new record to a table (I was originally trying to run an append query and key getting key violation errors that made no sense)

    The primary key is an sequential autonumber field. For some reason the db is trying to re-use an already used number when adding a new record. Any ideas why this is happening and what I can do to get around this.

    The data in the table is an almalgamation of a number of years work (approx 55000 records) and the use IDs used in fairly distinct chunks. The minimum number is 56062 whilst the highest number is 115642.

    I don't understand why, after a compact and repair, the db doesn't try to use 115643 as the next ID

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-use of autonumber causes violated (2000/XP)

    There has been an intermittent problem with duplicate autonumbers in recent versions of the Jet Database Engine that underlies Access. See ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database.

    See How to reset an AutoNumber field value in Access for several methods to correct this. Our moderator Mark Liquorman has a shareware utility that resets the AutoNumber correctly: Liquorman, Inc.: Access Tips, Links, & Downloads.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Manchester, Gtr Manchester, England
    Posts
    299
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-use of autonumber causes violated (2000/XP)

    The service pack didn't seem to work but Mark's utility did - problem solved. Thanks

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-use of autonumber causes violated (2000/XP)

    My home pc has a Msjet40.dll version that's earlier than 4.0.2927.4. Since I don't want to install XP SP2, I've tried to use WindowsXP-KB829558-x86-ENU.exe as mentioned here but unfortunately it doesn't work for XP in Italian, can I just copy Msjet40.dll from my work pc(version 4.0.8618.0) and paste it on my home pc?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Re-use of autonumber causes violated (2000/XP)

    I doubt it, installation is more than just pasting a file. You could try though, but keep the old version so that you can put it back if the new one doesn't work.

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Re-use of autonumber causes violated (2000/XP)

    Thank you Hans <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

Posting Permissions

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