how do I add an auto-number field to an existing database?

[Guest]

After I'd already started entering info into a new database, I decided that I
did want autonumbering after all. When I tried to add an autonumber field,
it tells me that autonumbering can't be applied to databases that already
have data entered. What gives? Isnt' there a way around this?

 

[Ken Snell [MVP]]

You can add a new field to the table, and make the new field an autonumber
type (so long as you don't already have an autonumber field in that table).
You can't change an existing field to an autonumber type after you've added
data to the table.

 

[Guest]

NOT AT THIS TIME

 

[Tim Ferguson]

After I'd already started entering info into a new database, I decided
that I did want autonumbering after all. When I tried to add an
autonumber field, it tells me that autonumbering can't be applied to
databases that already have data entered. What gives? Isnt' there a
way around this?

If you already have numbers that you want to retain as identifiers, then
you can keep them and add autonumbering on top. Make a couple of backups
first though!!

In the database window, copy the table and then paste it back as
Structure Only. Delete the old number field and replace it with an
Autonumber field with the same name.

Now in the Query Designer, make an append query that copies all the
records from the old table into the new one: including the old number
field into the new number field.

Once you are happy that all the records look okay, you can delete the old
table, removing any relationships first, though. Rename the new one to
the old name. Check all the indexes and re-establish the relationships.

The new table will start its autonumbering at one more than the highest
number that you imported. Easy!

Hope that helps


Tim F