Want to change table's key field to autonumber

[alguzman]

Table has information in it already and the key field was set to number when created. There are 1033 Records and I now want to change it to autonumber but the records do not go 1-1033 in order some numbers skip along the way so my last key field is 1041 with 1033 total records. I want to have the next new records to increment automatically after 1041 so the next new record will be 1042 and so on. Any suggestions

 

[AlanS]

Add the eight "missing" records to your existing table, using the "missing" numbers in the key field. Then copy the table's structure only to a new table. Then delete the key field from the new table, and add a new autonumber key field to it. Then copy all the records from the old table to the new one (they should come over in key order). Finally, delete the eight bogus records from the new table.

 

[DJBummy]

Just a friendly reminder! Back up your database before changing the field. Probably won't matter but I have had instances when changing field data types and was very sorry I did not make a copy of database.


DJ


[This message has been edited by DJBummy (edited 08-21-2001).]

 

[Pat Hartman]

Copy the definition of the existing table to a new table. Change the definition of the key field from number to autonumber. Then write an append query to select all the columns from the existing table and append them to the new table. Your number will be transferred to the autonumber field in the new table and the autonumber will continue numbering at n+1 where n is the highest value appended by the query.

Don't worry about the missing numbers. They will not cause a problem.