Hi there
In my database, there is field called Student_ID which is a long integer
field which someone has created manually.
I want to have all new students to have IDs that are autonumbers.
Is it possible to keep the current IDs as is and have any new people have
their IDs created automatically?
Thank you in advance for your help
As Gina says, you can't change an existing Number to an Autonumber.
What you can do is tedious but works:
- MAKE A BACKUP! of your database
- Turn off Name Autocorrect (if it's on, leave it off if it's off, and *leave
it off when you're done*, it does more harm than good!)
- Check the backup, make sure it works
- Open the Relationships window, add this table, and click the Direct
Relationships button to show all relationships to this table
- Note down which tables the student table is related to
- Select each join line (the line, not the table icon!) in turn and press the
Delete key to delete all the relationships to this table
- Close the relationships window
- Click on (but don't open) the table in the Tables window
- Press Ctrl-C then Ctrl-V to copy and paste the table to a new copy of the
table. Select the option "Design View Only" to create an empty table.
- Open it in design view, change the Student_ID to Autonumber
- Run an Append query to migrate all the data from the old table into the new
one (you can append into an Autonumber, the only way you can control its
value)
- Rename the old database (to Students_OLD say)
- Reestablish all the relationships to the new table
- Test everything
- If all is well, delete Students_OLD and then compact & repair
