Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    May 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have received a database from a third party which has been created using the shell of an existing database however the primary key values were not reset to zero, consequently my Primary Ked ID numbers start at 135. How do I reset these numbers to Zero?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    If you don't have any real data, you can delete all values then perform a "compact and repair" - then next time you enter data they will start at 1 again.

    To perform a Compact and Repair, go to the Office button, then Compact and Repair is one of the options under Manage.

    But if you have real data, and you want to change the values in the key it is a different story.
    Regards
    John



  3. #3
    New Lounger
    Join Date
    May 2007
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by johnhutchison View Post
    If you don't have any real data, you can delete all values then perform a "compact and repair" - then next time you enter data they will start at 1 again.

    To perform a Compact and Repair, go to the Office button, then Compact and Repair is one of the options under Manage.

    But if you have real data, and you want to change the values in the key it is a different story.
    Thanks John,
    I have found the compact and repair function but that does not heelp me as I have real data in the tables. I tried exporting the table to Excel, renumbering the Primary Key ID column and then re-importing into the database (after renaming the original table) but it returns a Run Time error.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    If you already have data in the tables, you should not try to change the AutoNumber. An AutoNumber field is a basically meaningness number that is guaranteed to be unique. It doesn't really matter whether it starts at 1 or at 135.


  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by Cliff584 View Post
    Thanks John,
    I have found the compact and repair function but that does not heelp me as I have real data in the tables. I tried exporting the table to Excel, renumbering the Primary Key ID column and then re-importing into the database (after renaming the original table) but it returns a Run Time error.
    I agree with Hans, however..
    How complex is your database? Do you have other tables that relate to the one you are concerned with via the Primary Key?
    If not exporting and importing would work, provided you don't import the primary key. i.e. export your data, then delete all the data, then compact and repair, then import. Access will assign a new Primary Key autonumber starting at 1.

    Another option is to change the autonumber field to a number (long integer) then add a new autonumber field. Then rename fields, and make the new one the key. But again you should not do this if other tables use the primary key as a foreign key, and have data in them.
    Regards
    John



Posting Permissions

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