Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autonumber for PrimaryID (2003)

    Hello,

    I have a table that has a primary Key with the data type set as AutoNumber. I been adding and deleting rows of information. Now that I set up the table the way I want to with the information I want to, how do I reset the autonumber to be in consecutive order. Any help would be great. Thanks.

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

    Re: Autonumber for PrimaryID (2003)

    An AutoNumber field is basically a meaningless unique identifier. There's no need for it to have consecutive values.
    If you want to display the records in a particular order, you can create a query that sorts the records the way you want.

    If you really want to, and if there are no other tables yet that link to this table by its primary key, you could create a new AutoNumber field, make it the primary key, then delete the old one. If there are other tables that use the current primary key as link field, it's still possible but a lot more complicated. And, as I mentioned, it's not really necessary.

  3. #3
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber for PrimaryID (2003)

    Thanks Hans. But what if I still need to change it to a consecutive number and there is one table link to the primary key. How do I do it?

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

    Re: Autonumber for PrimaryID (2003)

    Once again, you don't *need* to make an AutoNumber field consecutive. It's not what AutoNumber fields are intended for. You may prefer to do so for aesthetic reasons...

    The steps are as follows. I'll call the tables A (with the AutoNumber field) and B.

    - Create a backup of the database, for if things go wrong.
    - Add a new field of type AutoNumber to table A. I'll call it NewID.
    - Create a new field of type Number (Long Integer) in table B.
    - Create a query based on A and B, joined on the old AutoNumber field in A and the related field in B.
    - Add the new number field from table B to the query grid.
    - Select Query | Update Query.
    - Enter [A].[NewID] in the Update to line for the number field (substituting the correct names of course).
    - Select Query | Run or click the Run button on the toolbar. This will fill the new field.
    - Close the query (you don't need it any more but you may want to save it in case you want to run it again).
    - Open the Relationships window.
    - Remove the relationship between A and B (involving the old AutoNumber field).
    - Create a new join from NewID in A to the new number field in B.
    - Double click the join line and tick the check box 'Enforce referential integrity'.
    - Close the Relationships window.
    - Open table A in design view.
    - Make NewID the primary key.
    - Delete the old AutoNumber field (and associated indexes).
    - Save and close table A.
    - Open table B in design view.
    - Delete the old join field.
    - Save and close table B.

    That's it!

  5. #5
    3 Star Lounger
    Join Date
    Dec 2006
    Posts
    250
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber for PrimaryID (2003)

    Hans, I am having an issue in doing another autonumber in table A. I get a message that says Microsoft Access allows only one Autonumber per table. I don't have a primary key in table B and a relationship of one to many (Table A-one to Table B many). I am sure I am doing something wrong. Any help would be great.

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

    Re: Autonumber for PrimaryID (2003)

    You're correct, I overlooked that, sorry! To get around this, start by deleting the relationship between the tables, then change the data type of the old AutoNumber field to Number (Long Integer) before creating the new AutoNumber field.

Posting Permissions

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