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

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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]

 

[Cyberwolf]

Open the table in Design view, then click on the field you want to make
the Key field and click the key icon on the toolbar. If you want to
add a field click on the first empty line name it ID make it an
autonumber field and click on the key icon in the toolbar.

Cyberwolf