Convert a field from long integer to Autonumber

  • Thread starter Thread starter forest8
  • Start date Start date
F

forest8

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
 
forest8,

Unfortunately, unless you have access to an SQL Server no way to turn that
field into an Autonumber. Fortunately you can use...

Dmax("YourIDField","YourTable") + 1

You can place that on the Before_Insert of your form and achieve the same
results as having an Autonumber field.

--
Gina Whipp
2010 Microsoft MVP (Access)

"I feel I have been denied critical, need to know, information!" - Tremors
II

http://www.regina-whipp.com/index_files/TipList.htm

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
 
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
Click to expand...

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
 
Back
Top