Compact/Repair DB Util not updating auto numbers
I am still very new to MS Access 2000 but have come across a problem which I do not know how to resolve, even if it is possible.
I have set up my DB and added all the relationships. A couple of the tables have an autogenerated number as one of fields and this is the PK on each of them.
I noticed a problem in that when creating a record, if you exit/escape out of the new record creation before adding the record, the auto generated number is lost forever. I found out that if you use the DB Utility, compact and repair, this will re-enable all these lost numbers and seems to work on most tables.
I have noticed that this does not work on two of my tables with this type of field. I have checked the records added and there are no links to other tables, so there should not be any problems, also it works on other tables with similar autogenerated number PK's. I have tried deleting the column and recreating it, and although this renumbers the existing records correctly, as soon as you cancel the entry of a new record, that number is lost and compacting will not bring it back.
Is this a known issue on Access? Is there any other setting I should be looking for? Is there any other way of updating this autonumber?
Apologies if this question has been asked before. I have had a quick look on the forums, but could not locate the answer.