Just curious, when I imported data from a spreadsheet with 519 lines into an empty table, why did my autonumber keys start at 56,557,618? How big can this get? I don't want to end up running out of digits for my primary key field as I didn't even start on the project and I expect to be dumping spreadsheets in quite regularly.
+1
A:
Access remembers the last ID it gave out even on an empty table. To reset the autonumber you have to compact/repair the database after emptying the table
Also the autonumber is a long integer which in Access can go up to 2,147,483,647
DJ
2009-06-04 21:43:27
Any empty table? This spreadsheet dump was the first time the table was hit.
NickSentowski
2009-06-04 21:47:39
You mean you drop and create the table each time? or are you reusing the table?
DJ
2009-06-04 21:48:35
The first time I used the table, my key was 56mil.After seeing that, I killed the table, re-created it, dumped again, and got similar results. When I compact and repair, will I get back "deleted" keys?
NickSentowski
2009-06-04 21:50:19
Do you have the AutoNumber generating sequential or random keys?
DJ
2009-06-04 21:51:23
You can override an autonumber field using a query, and the new numbering will start using the override value. Even if you delete records, the numbering will not reset. Is that what might have happened?
dsteele
2009-06-04 21:53:08
You'll have to empty the table and compact in order to get the numbers back.
dsteele
2009-06-04 21:53:57
Sequential... Hence the confusion. My thought was that Access may have used some kind of date/time stamp for imports. To be honest, I don't really care what they key values are, just as long as I have enough keys to go around.
NickSentowski
2009-06-04 21:54:16
Well something very weird is going on then - a fresh table with a sequential autonumber will always start at one...
DJ
2009-06-04 21:57:20
It will start at one unless it's created with a seed value of something else. Or perhaps it's corrupt. I'd try a fresh blank database and see what happens if I create the table there and append the data. If it works, there's something wonky about the original DB and it needs to be recreated.
David-W-Fenton
2009-06-06 04:35:12
+1
A:
You can reset the autonumber without compact and repair using :
CurrentDb.Execute "ALTER TABLE tblTable ALTER COLUMN ID COUNTER(1,1)"
You must make sure the table is empty before running the SQL or you will get Access trying to create duplicate autonumbers.
Remou
2009-06-04 21:54:12
Does this need to be run in VBA? I thought ALTER TABLE was an SQL command, but it wouldn't run for me.
NickSentowski
2009-06-05 15:08:58
As it is written above, it would run in VBA, but you can paste ALTER TABLE tblTable ALTER COLUMN ID COUNTER(1,1) into the SQL view of the query design window and it will run. You do not have to use Counter(1,1) unless you wish to restart from 1, 100,1 would be fine.
Remou
2009-06-05 15:43:50
A:
You can also reset the autonumber by creating an append query that includes the autonumber column. Once a row is appended then the number will be set to the appended value + 1.
Doing this you have to be careful that the value is not less than the highest current value.
For e.g.
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 reset the autonumber to + 2 of the last number. Note you would have to include any mandatory columns and delete the new row afterwards.
Mark3308
2009-06-05 13:15:58