Autonumber field reset (1 Viewer)

mrrayj60

Registered User.
Local time
Today, 02:54
Joined
Sep 3, 2009
Messages
103
Is there a way to tell the autonumber field to use a specific number next. Somehow my numbering jumped from 5259, 5260 etc to 16314512, 16314513 sequence. Because these numbers are so high even deleting the records does not let me go back - I fear the only option is to export and re-import the data :banghead:
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 02:54
Joined
May 21, 2018
Messages
8,463
Normally you would not worry about trying to keep sequential numbering. If you are you should not be using an autonumber field. However, unless you have done some big imports and deletes, I would be a little concerned about why such a big leap. Lots a reasons for smaller leaps. Make sure to do a backup and then a compact and repair. If you want to reset the numbering see

https://support.microsoft.com/en-us/help/812718/how-to-reset-an-autonumber-field-value-in-access

Make sure if you have referenced tables you use the second method. If you use the first and lose the primary key, you will not have the ability to reestablish your relations. And again, before doing this make a backup.
 

Gasman

Enthusiastic Amateur
Local time
Today, 06:54
Joined
Sep 21, 2011
Messages
14,048
Is there a way to tell the autonumber field to use a specific number next. Somehow my numbering jumped from 5259, 5260 etc to 16314512, 16314513 sequence. Because these numbers are so high even deleting the records does not let me go back - I fear the only option is to export and re-import the data :banghead:

Yes, someone asked this question just the other week.

I can't find the thread, but I use this to reset my tables ready to go live.

Be aware that I am deleting all my records and resetting to 1, so comment out the sql delete line and always make a backup before trying it out.

I believe the first 1 in (1,1) is the start and the second the increment?

Code:
Sub ResetTableNumber(pstrTable As String, pstrID As String)
Dim strCmd As String, strSQL As String
strSQL = "DELETE * FROM " & pstrTable
strCmd = "ALTER TABLE " & pstrTable & " ALTER COLUMN " & pstrID & " COUNTER(1,1)"
CurrentDb.Execute strSQL
CurrentDb.Execute strCmd
End Sub
HTH
 

mrrayj60

Registered User.
Local time
Today, 02:54
Joined
Sep 3, 2009
Messages
103
Thanks, method 2 worked for us and they are back up and running-Ray
 

MarkK

bit cruncher
Local time
Yesterday, 23:54
Joined
Mar 17, 2004
Messages
8,178
I think you can also restart an Autonumber ID field if you just copy the table, and paste it back into the database, and select the "Structure Only" option in the "paste options" dialog.
Steps:
1) right-click on a table in the nav pane and click "copy" in the popup menu
2) right-click again in the table list, but not on any table and click "paste" in the popup
3) a "paste options" dialog opens
4) select the "structure only" option and click OK.
Your table will be saved with the new name, no data in it, and a restarted AutoIncrement ID.
hth
Mark
 

Users who are viewing this thread

Top Bottom