Is it possible to Change Primary Key from Input to Autonumber?

[BrianThompsonTenTen]

I have a large table that was created while manually entering a [ClientID] which was used as the PrimaryKey. my question is can I keep the "old" [ClientID] and AutoNumber generate [ClientID] moving forward? From what I understand the answer is no, if this is true is there a workaround,

 

[xenou]

from what I recall you can create a new table with the autonumber key, then insert the records into it (keeping existing values) ... if there are no duplicate keys of course. Naturally you'd have to change the table name temporarily during the transition so you don't have two tables with the same name.

 

[jackd]

Changing a Primary Key is never something to take lightly.
If your current table is involved in multiple forms, queries, procedures, then your probability of problems is high.
If, however, you are just starting and this is your only table, or table that is not yet related to any others, you could do something along this logic.
As xenou suggested,
-create a new table with an autonumber pk., suggest you name it something like ClientPK (add any other fields not in your current table that you feel are needed, if any)
-if the manually entered data is meaningful to you, you could add a field to the new table eg OldClientId or similar and when you dd existing records to your new table, put the original ClientId in that field( then you'll have some history for existing records)
-use an INSERT query to add existing records to the new table.

More info from M$oft.

There may be other considerations if we knew more about your specific project and the reason for considering the PK change.
Good luck with your project.