Autonumber in 'add record via SQL append qry is out of range expected

[ArviLaanemets]

In case you need to create new autonumbers for some table:

Find a time, nobody uses database (or simply restrict access for other users for period you need);
In table where you want new autonumbers (e.g. table MyTable with field MyKey), add a field e.g. MyOldKey (Long Integer);
Copy the table with new name (e.g. MyOldTable);
Delete all entries in MyTable, and COMPACT THE TABLE after that.
Use Insert query to import all records from MyOldTable into MyTable - the cuery must not have field MyKey (it is generated automatically), and must have field MyOldKey filled with values from field MyKey in MyOldTable
In any tables which have a foreign key linked to autonumeric field in MyTable, add a field MyOldKey (Long Integer);
Update every linked table, setting MyOldKey = MyKey, and MyKey = Null (In case the field is unique index somehow, or with Null's restricted, remove the index or restriction until the end of processing);
Update every linked table, setting field MyKey = MyTable.MyKey where MyOldKey = MyTable.MyOldKey;
From every linked table, delete records where MyOldKey remains empty (Those are 'orphan' records);


Delete table MyOldTable, and field MyOldKey from every table processed.

[hammerman]

Thank you all for your help!
I used Gicu's recommended process, essentially: I deleted the records that were the problem; {saving that info elsewhere first}, then exported both tables to Excel, and matched the two tables keyed on the auto number key, and removed orphans; Then imported the Excel files, {renaming the old ones first}, and the issue is gone. I'm able to add the 20 + new records that I couldn't before. I also checked the 'similar threads' links below. All is well. Thank you all, again!