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

    Autonumbers and Compacting (Access2000+)

    Did I just not hear about this?

    Once upon a time (Access97), if you compacted a table that had an autonumber field and if you had deleted records with the highest used autonumbers, the compacted table had its autonumber seed set to the last used autonumber. So for example, if you had 2500 autonumbers (1-2500) and deleted autonumbers 2001-2500 and then compacted, the next autonumber to be assigned would be #2001.

    Apparently in Access2000 and 2003 this is no longer the case. Compacting does not affect the autonumber seed value. The only exception appears to be if the table is completely empty; then it works as before.

    Seeing as how it took me this long to discover this, it obviously isn't a big deal to me. I'm just worried about what else I didn't hear about!
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Autonumbers and Compacting (Access2000+)

    It's a problem with the Jet Engine - the error was introduced in Jet 4.0, repaired, then re-introduced and now it seems to be there to stay.

    See AutoNumber field is not reset after you compact an Access database.

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

    Re: Autonumbers and Compacting (Access2000+)

    I knew it didn't reset if the seed value was less than the last autonumber used (as could happen after using an append query). But I had thought it reset under "normal" circumstances. There oughta be a law! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

Posting Permissions

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