Results 1 to 9 of 9
  1. #1
    LouiseRoussel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6

    How to reset AutoNumber in Access 2016 using Database Tools / Compact & Repair Database

    Hello all.
    I am teaching Beginner's ACCESS and I have come across a problem with resetting AutoNumber.
    As my students are creating new Tables etc...often they find themselves deleting records and therefore would like AutoNumber to reset to 1 in their DataSheet.
    I saw a quick fix on Internet: Basically going to DATABASE TOOLS, then COMPACT & REPAIR DATABASE and then right click to open the Table (from the navigation pane).
    Sometimes it works beautifully and resets to 1 and other times (Most of the time) it does not work.


    Can you explain to me why. I have not figured out the mechanism as to why it sometimes works and why it sometimes doesn't.
    Thank you TEAM AccessForums.
    Louise Roussel

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Delete records. Run C&R. This always resets the autonumber seed for me. I have never seen it not do that.

    Only resets to 1 if all records in table are deleted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    LouiseRoussel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Thanks. But did not want to delete all records. Well, I guess that means that they have to delete AutoNumber primary key field in Design View and toggle Primary Key to remove (plus remove any relationships if any) and then reenter field. Is this the only way? thanks again, LR

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    It sounds like you want a gap-less PK field.


    AutoNumber
    ----------------
    Purpose: Use an AutoNumber field to provide a unique value that serves no other purpose than to make each record unique. The most common use for an AutoNumber field is as a primary key.



    Autonumbers--What they are NOT and What They Are

    Microsoft Access Tables: Primary Key Tips and Techniques

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    If you must have an identifier that does not have gaps in sequence, autonumber is not ideal. It can be done but is a pain to implement and manage.

    As ssanfu links explain, autonumber field is not intended to have meaning to end users, only to db designer when setting relationships. End users should not even be aware they exist, don't even have to see them. Gaps should be irrelevant.

    If you re-build autonumber key field, then parent/child relationships for existing records are broken and data is corrupted.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    LouiseRoussel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Thanks all.
    Yes I know that the purpose of Primary Key AutoNumber is "uniqueness".
    I guess I was dreaming.... these students are just entering a few records (3 to 5) in each table and they get frustrated really easily when they see that their AutoNumber Primary Key has skipped due to erroneous entries.
    Thanks all for your help.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Suggest part of instruction includes to ignore these gaps.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    LouiseRoussel is offline Novice
    Windows 10 Access 2016
    Join Date
    Jun 2019
    Posts
    6
    Yes you are right. They will have to learn to deal with gaps. Have a nice evening.
    Sorry to waste your time on this.
    Case closed.

  9. #9
    Join Date
    Apr 2017
    Posts
    1,673
    In case you are deleting and adding a number of records frequently, then it is better not to use autonumber at all! In this case the better choice is e.g. one-or multi-field Primary Key (an unique combination).

    Resetting the seed of autonumber sets it to biggest existing one +1. I.e when you have e.g. 10 records with biggest remaining autonumber from 200 entered previously being 100, then resetting is putting the seed to 101.

    To fill autonumbers anew from 1, you have:

    1. Create a copy of table and read data into copy;
    2. Delete all data from original table and reset autonumber (the seed must be 1)
    3. Add into original table a long integer field for old autonumber values;
    4 Run an INSERT query to fill original table with data from copy table. Autonumber values must be written to added field.
    5 Run an UPDATE query for every table having autonumber value from original table as foreign key to replace old FK value with value of new PK in original table.
    6. Delete added field from original table. Delete copy table.

    Thorough this you must be a single user of database!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Compact & Repair and Autonumber issue
    By Roncc in forum Access
    Replies: 11
    Last Post: 07-08-2018, 11:57 PM
  2. Compact and repair/ close access 2016
    By Simonhtc4 in forum Access
    Replies: 8
    Last Post: 04-20-2017, 11:15 AM
  3. Split Database | Compact and Repair
    By mrmmickle1 in forum Access
    Replies: 4
    Last Post: 07-27-2015, 08:53 AM
  4. Compact and Repair Encrpyted Database Problem
    By mrlddst in forum Security
    Replies: 16
    Last Post: 05-06-2014, 01:24 PM
  5. Replies: 7
    Last Post: 11-11-2010, 11:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums