Results 1 to 6 of 6
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Resetting Autonumbers! (Access 2000)

    Hi,
    I was under the impression that I needed to compact and repair the database in order to reset the autonumbers. It does not seem to be doing this!!!
    Say I have 10 records. (P/K is autonumber) I delete records 8-10! Obviously the next record has 11 as an autonumber. When I compact the DB, the number stays at 11.
    What am I missing! <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Regards,
    Rudi

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

    Re: Resetting Autonumbers! (Access 2000)

    Resetting autonumbers on compacting a database was broken in one of the updates for Jet 4.0, repaired in a later one, then broken again. In short, you can't depend on it.

    It shouldn't really matter. Autonumbers are meant as a convenient way to create a unique ID for records, they have no intrinsic meaning, Many Access users would prefer to have a contiguous range of IDs for aesthetic reasons, but there is no real need for it. Gaps within a range of autonumbers aren't filled anyway (although there was a bug that caused that to happen, see ACC2000: AutoNumber Field Duplicates Previous Values After You Compact and Repair a Database).

    If you really want to, you can use the technique described in ACC2000: How to Use an Append Query to Set Initial Value of an AutoNumber Field to reset the autonumber field manually.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resetting Autonumbers! (Access 2000)

    Hans, thanks a stack for those links. I am aware that it really does not matter what the autonumber value is, but I remember being able to clean out test data, and reset the DB sothat the new actual data starts at autonumber 1 again! I was also demo'ing this to someone when it did not work! I could not figure out why.
    Thanks
    Regards,
    Rudi

  4. #4
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting Autonumbers! (Access 2000)

    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Resetting Autonumbers! (Access 2000)

    If you check my website (see below), I have a couple of utitlies that will help reset the autonumber. The ADO solution is the only one that will reset it to a lower #. The DAO solution will help resolve the duplicating autonumber issue, but will reset it to a lower #.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Resetting Autonumbers! (Access 2000)

    Thanks guys...the links you have provided, and your support is highly appreciated.
    I feel completely informed and equiped!
    Cheers
    Regards,
    Rudi

Posting Permissions

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