Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber less than previously used value (XP SP2)

    I have a database that has started exhibiting abnormal behaviour with Access XP. Autonumber fields are returning values that have already been used, causing key violations for the user. The database is a FE BE with FE's installed on XP Pro workstations. There are no more than 4 concurrent users of the system and the BE size is only 2.5Mb.

    Obviously I have repaired the database and reset the Autonumber field by appending a new max value. The problem is that other tables has exhibited the same behaviour and there is no guarentee that the problem will not reoccur. I'm looking for reasons and a fix.

    I'll replace the autonumber field with a long and write a routine to handle the incrementing if I have to but I'd prefer to understand the root cause and correct it if I can.

    I've searched for a solution or information on this behaviour but have had little luck.

    Has anyone else seen this sort of thing happening and know of a solution or be able to point me in the direction of more information?

    Thanks

    Stewart

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

    Re: Autonumber less than previously used value (XP SP2)

    According to Microsoft, this was a problem with early versions of Jet 4.0. It should be corrected in later versions - see AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database. Unfortunately, these later versions have another side-effect: an AutoNumber field will not always be reset to start at the first available number after a compact and repair, but that is less problematic.

  3. #3
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    Hi Stewart

    I have been chasing this same problem for 12 months now.

    Customer was using A2k (9.0.4402) SR-1 Jet 4.0 SP-4 when problem started, custonmer is now using a2k (9.0.6926) SP-3 Jet 4.0 SP-7 with sam problem

    Using FE and BE DB

    I tried decpmpile and importing all objectes into a new DB with same problem

    It happens intermitly and not necessarily after a Compact and Repair

    This problem happens on customers computer with same table and same form only. It

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    Hi John,

    I'm glad I'm not alone. Did you see the reply from Hans?

    You said <reset the Autonumber field by appending a new max value.> How do you do this?

    Simply by using an append query to add the new value to the autonumber field, sequential numbers then start from the new seed.

    Thanks for taking the time to reply.

    Stewart

  5. #5
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Emerald Beach, New South Wales, Australia
    Posts
    126
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    Thanks Hans as always you are quick to respond. I dont know how you manage to get any work done though.

    I'll read the article and ponder my option.

    Thanks again.

    Stewart

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

    Re: Autonumber less than previously used value (XP SP2)

    It is possible programatically to inadvertently add a duplicate number in an autonumber field through errant code and other similar situations. I would suggest you take a long hard look at that kind of possibility - we run many databases with autonumber fields and have ever only seen one situation - a know bug in early versions of 2000 - where we actually got duplicates. All the other cases we've seen were situations where the database had corrupted, or ones where an errant form or code was doing things it shouldn't.
    Wendell

  7. #7
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    If I can scale down a sample db with form that errors intermitly, would anybody be interested in checking for misbehaving code?

    John

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

    Re: Autonumber less than previously used value (XP SP2)

    Go ahead, you know the rules (zip file < 100 KB). If the problem is intermittent, we can't guarantee that we'll see it or find a solution.

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber less than previously used value (XP SP2)

    I have to agree with Wendell. I encountered a problem in Access 2000 with "duplicate" autonumbers popping up after records were appended to a table and the autonumbers were also appended. This was NOT fixed by compact and repair, which did not reset the seed. Office 2k SR-1 and Jet SP4 addressed those issues. Later Jet service packs reintroduced the problem of the seed not being reset by compact, but this has apparently been addressed in SP8. I have never seen a duplicate come up otherwise except when it was introduced by either the program or the way the application was distributed to various machines.
    Charlotte

  10. #10
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    If I understand you correctly there are two issues here:

    1. Errant code as suggested by Wendell could cause my duplicate autonumber proplem

    2. If I do get the autonumber problem Detect & Repair & SP8 will reset the autonumber seed to highest, which would in effect allow my operator tp proceed appending records as described in Post 319900

    Thanks, John

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

    Re: Autonumber less than previously used value (XP SP2)

    I don't think it is errant code or the Compact/Repair. I think the problem is with append queries (we hit it while doing append queries to import data), and unfortunately the Compact/Repair won't fix it. If you want to follow the link below to my website, I've got a couple of solutions (DAO and ADO) to help repair the problem in a table once you've determined you have one.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  12. #12
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    Hi Mark

    Thanks for the code.

    Does this mean when my operator gets "Duplicate Values In The Index Message" she can exit the system. run your db and code to reset the autonumber?

    Does your code return any feedback that there was a problem with the autonumber and the autonumber was reset to the highest value?

    John

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

    Re: Autonumber less than previously used value (XP SP2)

    I don't think it returns any values, but you can add it yourself. The ADO solution requires that the target database (or at least the target table) not have anyone else working in it at the same time. The DAO solution does not have that same restriction, although it probably is a good idea not to have anyone else in it at the same time.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  14. #14
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Niagara Falls, New York, USA
    Posts
    1,878
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber less than previously used value (XP SP2)

    Thanks Mark

    In Charlottes <post#=321689>post 321689</post#> above does Jet SP8 Detect and Repair also sovle this problem?

    John

    (Edited by HansV to make post reference clickable)

  15. #15
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autonumber less than previously used value (XP SP2)

    I'm not sure what you're asking. Detect and Repair is an item on the Help menu of MS apps. Compact and Repair is a Jet engine process. Detect and repair is for fixing an installation. Compact and repair is for compacting a database.
    Charlotte

Page 1 of 2 12 LastLast

Posting Permissions

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