Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Cool Unanswered: Modify autonumbers

    Ok, I have a user who had a database created for them. The ID field is a autonumber and some records have been deleted. She want to keep them into sequence with the autonumber field. Is there a way to pull the data out and then re-enter it with a auto-number ID in Sequence???????

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Select all records and columns except for the autonumber column. Copy to clipboard. Copy table struture to new table. Open and paste columns anew ... Delete old table.

  3. #3
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    Or how about just adding a new autonumber field to the table and removing the old one. And after that give the new field the same name as the old one.
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  4. #4
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    As a safety measure I add a temp numeric field to the table and update it's value to that of the old autonumber prior to deleting and re-adding a new on - so that I know the result is correct.

  5. #5
    Join Date
    Jun 2003
    Location
    USA
    Posts
    1,032
    And of course you'd want to do A LOT of "being careful" stuff if you actually have any related records in other tables...
    J. Paul Schmidt, Freelance Web and Database Developer
    www.Bullschmidt.com
    Access Database Sample, Web Database Sample, ASP Design Tips

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Smile Modify Autonumber

    Ok that was a great response. Do the same rule apply to a Access Project file????

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink Modify Autonumber

    Will this adding of the field have any affect on any previously designed forms. I'm looking at the future of rolling this up to the web at some point. If my memory is correct I just need to go to properties and re-assign the new field to the oldID field on the form

  8. #8
    Join Date
    Oct 2001
    Location
    Chicago
    Posts
    440
    no problem - you are left with the same fields when you done

    easiest way:

    1. open table in design view
    2. rename old autonumber field and change type to number
    3. save table
    4. add new auto number field using the original name
    5. save table
    6. check values
    7. delete the original autonumber field

    you may have to reset primary key and relationships if any previously existed

  9. #9
    Join Date
    Nov 2003
    Location
    San Francisco, CA USA
    Posts
    59

    Dodgy practice

    BTW, if it's not too cumbersome or too late, I'd recommend that you create a new number field that the client uses for her purposes and leave the autonumber field alone.

    Users are notorious for wanting to tinker with that number if they see it and rely on it. You can fix it now but it will get out of sync later when (if) records are deleted in the future.

    Also, if the db uses the autonumber as a foreign key in other tables, renumbering this one could really mess up your data.

    Solution:

    I have some apps where I run code to check the last ID number used in a dataset and assign the next available number to the ID field. Then I let my autonumber PK field do its own thing.

    Much easier!

    Also, Rockey's way works like a charm!! It's what I do when I'm forced to deal with this for other reasons. Plus you have the added benefit of validating your data before you delete the old field.

    Magee
    Last edited by mageem; 02-04-04 at 00:40.

Posting Permissions

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