Results 1 to 9 of 9
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Resetting an AutoNumber Field to Zero (Access 2000)

    I'm sure we are all longing to see the search feature back <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.
    In the meantime, a question that I'm sure has been asked many times before;
    How do you reset an AutoNumber field back to zero? I'm fooling around with a form - but I know when I take my DB live and delete the fake data I've placed in the table, that I'll want to reset the AutoNumber field back.
    Thanks guys!
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    That depends. In Access 97 and earlier, deleting the records from a table and then compacting the database would reset the autonumber seed so that it again started at 1. In Access 2000, the same thing never worked quite right, but it was fixed in SR-1. Unfortunately, theJet service packs after number 4 (I think) broke it again, so it doesn't work in Access 2000 or 2002 with Jet service packs 4 or higher applied. In that case, your only option is to create an empty copy of your original table and then replace the original with it. Annoying, huh?
    Charlotte

  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: Resetting an AutoNumber Field to Zero (Access 2000)

    But one might ask: if seeing the value of the autonumber is important to you, should you really be using an autonumber in the first place?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

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

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    I think most of us who use autonumber keys prefer to reset the seed before releasing the application to the user. It just feels cleaner to have everything start from 1 rather than 5237, etc.
    Charlotte

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

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    Oh, another alternative is to create a new database and import all the objects from the original. You can import the data structure only for the tables. That should reset the seed on all the autonumbers.
    Charlotte

  6. #6
    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 an AutoNumber Field to Zero (Access 2000)

    I agree that it "feels" cleaner and I try to do it also, but it really shouldn't matter. But my point was related to determining if this was a situation in which it did matter. Then you get into a host of other issues from the user, like "why do I have gaps in my sequential numbers". Then use of autonumber may not be appropriate.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    The I'm glad this came up because I wanted to discuss a couple of issues with it for a long time!

    I, like most people I suppose use the auto number feature to generate a unique number for a record. I understand the issues with using the system for generating a number for an invoice, et cetera because of the likelihood of having missing numbers in the list.

    However I did have an annoying occurrence where I have regretted using auto numbers. What happened was I wanted to replace the data in a table on the customers server, someone had got in to the database and had deleted some of the rows in the table, which ones we weren't sure so we decided to replace all the data from a backup copy. Unfortunately the table structure had been changed since the backup copy was made and it was felt the best way would be just to put the backup data in the modified table . However this can't be done because the auto number will assign different numbers to each row, making the data useless.

    The only option was to copy over the old table and carry out the necessary alterations to its structure. Not a big job, not a problem, but it just showed me that there are some problems in using auto numbers as opposed to generating your own number.

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

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    If you use an append query to add records to a table with an AutoNumber field, and if you include the AutoNumber field in the target fields, it will be populated with the values from the source, not with new automatically generated numbers. See attached demo; it contains two tables and an append query. The autonumber sequence in tblSource contains gaps; if you execute the append query, the autonumbers will be copied "as is" to tblTarget.
    Attached Files Attached Files

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Newbury, Berkshire, England
    Posts
    712
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Resetting an AutoNumber Field to Zero (Access 2000)

    Well I'll be dammed! I didn't think an auto number field would accept values. Thanks Hans...

Posting Permissions

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