Resetting autonumber seed to the highest used value
Executing an append query which has a value in the autonumber field can set the autonumber seed to a unwanted high value. Or maybe you had some code running amok, or amuck if you prefer that spelling, which accidentally inserted a lot of records.
In Access 2000 and newer, or rather Jet 4.0, as well as Access 2007 and ACE, removing the unwanted records and compacting a database doesn't reset the autonumber seed back to the highest used number. The autonumber see is only reset to 0 when you remove all the records in a table. in Access 97, Jet 3.5, the seed was reset back to the highest used value.
(Of course you're going to have to believe me when I say I did a compact in between those two screen shots.)
Of course the purists will state that an autonumber has no real meaning and the users will never see it anyhow. Yes, they have a point but I like being neat and tidy, thank you very much.
See Fixing AutoNumbers when Access assigns negatives or duplicates for ADO code to reset the seed. Note that this is about the only feature in ADO or ADOX that DAO doesn't have when it comes to Access databases. There might be one or two other things but I've never needed them or they might only apply to SQL Server and other databases.