How do I add a Primary key # to a table that already exists?

[Marie]

I have a data table with over a 1000 entries, but they are not numbered at
all. I would like to add an autonumber and primary key, but when I try to do
so, it says I cannot add an auto number to a field when there is data already
entered in the table. Is there a way to number the current entries, as well
as continue to number the future entries?

 

[src]

Not sure if you can add an autonumber. But here is a work-around. Export
out the table to an Excel file, open in Excel add a column and number it 1
through you last number, save it, then export it back in your access database.

That is what I had to do.

 

[Ken Sheridan]

There is no reason why you can't add an autonumber column to a table, but if
you want sequential numbering an autonumber is not a guaranteed solution as
its designed to ensure uniqueness not necessarily sequence. To ensure the
latter its best to compute the numbers. In a single user environment its
easy to do with something like this in the data entry form's BeforeInsert
event procdure:

Me.MyID = DMax("MyID", "MyTable") + 1

You'll find a demo of how this can be done in a multi-user environment
without any conflicts if two users are adding a record simultaneously at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps

Ken Sheridan
Stafford, England

 

[Angie]

You could right click/copy the table...paste the STRUCTURE only...on this new
table go into design...add an autonumber field. Append records from old table
to this new table.

 

[John Vinson]

I have a data table with over a 1000 entries, but they are not numbered at
all. I would like to add an autonumber and primary key, but when I try to do
so, it says I cannot add an auto number to a field when there is data already
entered in the table. Is there a way to number the current entries, as well
as continue to number the future entries?

Create a new, empty table (copy and paste this table and choose the
option "design view only); add an Autonumber field; and run an Append
query to migrate the data from your current table into this new one.

John W. Vinson[MVP]