Reseting autonumber

[ItIsHardToProgram]

Is there a fast way to reset autonumber and reasigns numbers from 1 to x following an order....

 

[mhartman1]

Hello:

One way would be to simply open up your table in design view and delete the autonumber field, and then re-insert the field once again.

Regards
Mark

 

[ItIsHardToProgram]

I guess that would work... but I was afraid to lose the relations?

 

[ItIsHardToProgram]

Could I maybe add a new autonumber and have it replace the old column ?

 

[ItIsHardToProgram]

I need to stop hitting submit, without finishing, maybe i could update table with another table that contains an auto number field with the number of entry?

 

[Remou]

Quotes from my files.
Reset autonumber
================================================
JeremyNYC (Programmer) 15 Sep 05 14:47
thread705-1122927
Keep in mind, though, that there is no reason to reset the autonumber field.
That number should NEVER be relied upon to present any kind of meaningful data.
Autonumbers are guaranteed (more or less) to be unique. They are NOT guaranteed
to be sequential, so the notion of assigning any significance to them is one
that will lead you down a dangerous path. See my write-up here for a bit more
detail:

http://www.abcdataworks.com/id33.htm

- Jeremy

Resetting Autonumber
thread702-1122847
PHV (MIS) 15 Sep 05 5:43
reseting an autonumber field to 1

CurrentDb.Execute "ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

Reset Autonumber Field
thread702-1050735

apart from compact and repair

flyover789 (Programmer) 1 May 05 8:09
you don 't have to do the compact/repair thing. you can do it during runtime using the following code:

if your autonumber field (in the code I call it [ID]) is the primary key in your table, use:

Code
Function resetautonumber()
DoCmd.RunSQL "ALTER TABLE yourTable DROP CONSTRAINT [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER CONSTRAINT [ID] PRIMARY KEY;"
End Function

if it's not the primary key, use:

'Code
Function resetautonumber2()
DoCmd.RunSQL "ALTER TABLE yourTable DROP CONSTRAINT [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER CONSTRAINT [ID] UNIQUE;"
End Function

if you don't wish to index the ID field at all, use:

'Code
Function resetautonumber3()
DoCmd.RunSQL "ALTER TABLE yourTable DROP COLUMN [ID];"
DoCmd.RunSQL "ALTER TABLE yourTable ADD COLUMN [ID] COUNTER;"
End Function

 

[Golom]

Just a note about this

"ALTER TABLE yourTable ALTER COLUMN myID COUNTER(1,1)"

It does not affect any numbers that are already in the autonumber column. It just causes the next inserted row to start at "1" (the first parameter) and increment by "1" (the second parameter) thereafter. This is an excellent way to get duplicate autonumbers! If you already have
[tt]
Anum
1
2
3
[/tt]
Then you run the above ALTER statement and then insert two more rows you will then have
[tt]
Anum
1
2
3
1
2
[/tt]
Probably not what you want.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[Eupher]

Golom,

Assuming the autonumber is primary key with no duplicates, wouldn't this throw an error?

Ken S.

 

[Golom]

Yes ... and it does.

If the autonumber is not specified as primary and/or unique then Access will insert duplicate numbers in the autonumber column.

[small]No! No! You're not thinking ... you're only being logical.
- Neils Bohr[/small]

 

[ItIsHardToProgram]

Thanks for that elaborate post, and thanks for the links REMOU I understand now why you say it is a dangerous path to go through. The thing is I had the autonumber has a reference for my timesheets, might have to change that.....

Thanks