Just curious, after i imported data from the spreadsheet with 519 lines into a clear table, why did my autonumber secrets start at 56,557,618? How large can this get? I'd rather not finish up drained of numbers for initially my key area when i did not attempt around the project and that i be prepared to be dumping excel spreadsheets in quite regularly.
You are able to totally reset the autonumber without compact and repair using :
CurrentDb.Execute "ALTER TABLE tblTable ALTER COLUMN ID COUNTER(1,1)"
You have to make certain the table is empty before you run the SQL or else you will get Access attempting to create duplicate autonumbers.
Access takes note of the final ID it gave out even with an empty table. To totally reset the autonumber you need to compact/repair the database after draining the table
Even the autonumber is really a lengthy integer which in Access can move up to two,147,483,647
You may also totally reset the autonumber by creating an append query which includes the autonumber column. When a row is appended then your number is going to be set towards the appended value + 1.
Carrying this out you need to be careful the value isn't under the greatest current value.
INSERT INTO tblName ( id ) SELECT [tblName]![id]+1 AS [New Value] FROM tblName GROUP BY [tblName]![id]+1, tblName.id HAVING (((tblName.id)=(Select max(id) from tblName)));
Would totally reset the autonumber to + 2 from the last number. Note you would need to include any mandatory posts and remove the brand new row later on.