Modify a Table Field to Be Auto-Number

Topic

New Reply

I have search through Microsoft stuff, but can’t find any way to change the data type of a field to AutoNumber once data has been input. I have a field that would work much better as an AutoNumber. Any suggestions on options. Again, Thanks for your support. Judy

Reply | Quote

Replies

In the database window, highlight your current table, right click, click Copy. Position in a blank portion of the database window, right click, paste only the structure (not the data). Now you have a table with the same structure as your current table. Modify this new table by adding an autonumber field. Then, write an append query to append all the records from your current table into the new table. The autonumber field will have values written to it during the append process. Rename and use this new table.

Reply | Quote

Thanks for your reply and I can see how that would work in most circumstance. With this one I want my student number to become an auto number field, and I would like to maintain the numbers up to point XYZ. Your method gives an entirely new auto number field. I think I probably will just have to live with my current method. Thanks so much, Judy

Reply | Quote

You can create a new table with the same structure by copying the structure only from the existing table. Change the field in question to an autonumber, which will work because there is no data in the table. Then create an append query and append each of the fields in the old table to the fields in the new table, including the field you changed to an autonumber. As long as the original field was numeric, your values will be appended to the autonumber field, and the next record you enter should be one above the highest value you appended.

One warning, though. If you’re using Access 2000 and do NOT have SR1 applied, the autonumber will try to start at 1 instead of the next correct number. That’s one of the best reasons I found for applying the service release.

Reply | Quote

Thanks Charlotte, I will try this trick with my database. Your help is appreciated. Judy

Reply | Quote