Results 1 to 4 of 4
  1. #1
    CarolineK is offline Novice
    Windows 8 Access 2007
    Join Date
    Feb 2015
    Location
    Alberta, Canada
    Posts
    1

    Primary key reset


    How do I reset a primary key.
    I am copying a database I use every year with new entries - the entry ID is getting unwieldy - so I want to restart with 1 again. How do I reset the primary key without disturbing the rest of the database.
    Thanks

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Historically if you empty the table and then do a compact/repair, the autonumber gets reset. Worth a try anyway.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    How do I reset the primary key without disturbing the rest of the database.
    Is the PK an Autonumber???

    If yes, then read these:

    Autonumbers--What they are NOT and What They Are

    http://www.utteraccess.com/wiki/index.php/Autonumbers


    Microsoft Access Tables: Primary Key Tips and Techniques
    http://www.fmsinc.com/free/newtips/primarykey.asp


    Use Autonumbers properly
    http://access.mvps.org/access/general/gen0025.htm
    (pay attention the the 3rd from the last paragraph that begins "If you need a meaningful sequencer........")

  4. #4
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi Caroline,

    resetting the primary key in a database is never a good idea. Generally you only use the primary key for connecting the data from several tables in a one to many relationship. Changing the PK might break-up that relationship. Normally, the end user never gets to see the content of the primary key field, they look at, for example, the customer number which is a separate, unique field.
    From the information that you copy the database each year and start from fresh I get a bit the idea you're using Access as an excel workbook and not as a database system.

    Greetings
    Noëlla

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

Similar Threads

  1. Reset ComboBox
    By yagerlegi in forum Forms
    Replies: 1
    Last Post: 12-23-2014, 12:56 PM
  2. reset autonumber
    By slimjen in forum Forms
    Replies: 4
    Last Post: 07-22-2014, 05:31 AM
  3. AutoNumber reset
    By Seeletse in forum Access
    Replies: 3
    Last Post: 12-12-2012, 07:14 AM
  4. How to Reset Comboboxes
    By bbarrene in forum Programming
    Replies: 5
    Last Post: 01-23-2010, 11:11 PM
  5. reset primary key
    By emilylu3 in forum Access
    Replies: 1
    Last Post: 12-09-2005, 03:27 PM

Tags for this Thread

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