Every once in a while I see a question regarding wanting to reset an autonumber’s counter/seed value to a specific number.
 

Eliminating Gaps

If the numbers have a big jump in numbering between the last entry in the table and a new one you are creating, and you are simply wanting to eliminate this gap, then performing a Compact and Repair should do the trick.

So say you delete a large quantity of records from a table, performing a Compact and Repair will reset the value to the very next number in the sequence based on the current maximum value in the table.

If you wipe a table completely, then performing a Compact and Repair will set the numbering back to 1 (or whatever the set seed value is).
 

Setting/Resetting The Counter/Seed

If you wish to set the next available value to a specific value (control the numbering), then we can easily do so with ADO, DAO or even directly in the QBE (query).

The basic SQL Statement syntax being:

ALTER TABLE [YourTableName] ALTER COLUMN [AutoNumberFieldName] COUNTER(NewCounterValue, CounterStep);

Thus, a couple real life examples might look like:

ALTER TABLE [Contacts] ALTER COLUMN [ContactId] COUNTER(5102);

Or

ALTER TABLE [Table 1] ALTER COLUMN [ID] COUNTER(5100, 5);

Once we understand the basics, we can then create a simple VBA procedure around it, such as:

'---------------------------------------------------------------------------------------
' Procedure : ResetAutoIncrementCounter
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Set the autonumber of a table field to a desired value and set the its
'               step as well.
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
'             (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
' References:
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTableName    : Name of the table
' sColumnName   : Name of the AutoIncrement field
' lCounter      : AutoIncrement value to set for the next entry
'                   Must be larger than the biggest current entry or else collisions
'                   will eventually occur.
' lStep         : Size of the step between autoincrement values
' bPerformCollisionCheck : Ensure the lCounter value is indeed larger than any existing
'                           values to avoid the potential of collision/duplicates
'                           be very careful using False here!
'
' Usage:
' ~~~~~~
' ? ResetAutoIncrementCounter("Table1", "ID", 799)
'   Returns -> True
'
' ? ResetAutoIncrementCounter("Contacts", "ContactId", 5, 5)
'   Returns -> True
'
' ? ResetAutoIncrementCounter("Invoices", "InvoiceId", 300)
'   Returns -> False - because there are already 700 rows in the table!
'
' ? ResetAutoIncrementCounter("Lkup_Cities", "CityId", 300, , False)
'   Returns -> True
'
' Revision History:
' Rev       Date(yyyy-mm-dd)        Description
' **************************************************************************************
' 1         2008-07-29              Initial Release
' 2         2023-02-14              Update URLs of Refs and Copyright
'---------------------------------------------------------------------------------------
Public Function ResetAutoIncrementCounter(sTableName As String, sColumnName As String, _
                                          lCounter As Long, Optional lStep As Long = 1, _
                                          Optional bPerformCollisionCheck As Boolean = True) As Boolean
'https://learn.microsoft.com/en-us/office/vba/access/Concepts/Structured-Query-Language/modify-a-table-s-design-using-access-sql
'https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/alter-table-statement-microsoft-access-sql
'https://www.w3schools.com/sql/sql_ref_alter_column.asp
    On Error GoTo Error_Handler
    Dim sSQL                  As String
    Dim lMaxId                As Long

    If bPerformCollisionCheck = True Then
        lMaxId = DMax(sColumnName, sTableName)
        If lCounter <= lMaxId Then
            '??? MsgBox to the user ???
            Debug.Print "The requested seed value of '" & lCounter & _
                        "' is inferior to the current maximum value of '" & lMaxId & "'"
            GoTo Error_Handler_Exit
        End If
    End If

    sSQL = "ALTER TABLE [" & sTableName & "] ALTER COLUMN [" & sColumnName & "] " & _
           "COUNTER(" & lCounter & ", " & lStep & ");"
    CurrentProject.Connection.Execute sSQL
    'OR we could use the following line instead
    'CurrentDb.Execute sSQL

    ResetAutoIncrementCounter = True

Error_Handler_Exit:
    On Error Resume Next
    Exit Function

Error_Handler:
    '-2147217900 - Table is currently open, so can't lock the table to perform the update
    '         13 - Column isn't an AutoNumber/Type Mismatch
    '       2471 - Column doesn't exist
    '       3078 - Table doesn't exist
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Source: ResetAutoIncrementCounter" & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

Simple Reset

In its simplest usage, we can set the next available value by simply doing:

? ResetAutoIncrementCounter("TableName", "FieldName", 799)

which will then return True/False to indicate success or not.

Simple Reset With A Custom Step In Numbering

Did you know you can actually have autonumbers jump by specified intervals other than 1? You can make them jump by any amount you which: 1,2, 5, … You pick.

To setup a number to 25 with a step of 5 we would simply do:

? ResetAutoIncrementCounter("TableName", "FieldName", 25, 5)

To setup a number to 2648 with a step of 2 we would simply do:

? ResetAutoIncrementCounter("TableName", "FieldName", 2648, 2)

Reset Value To Fill-in Gaps

By default the function ensure you only enter a new seed higher than the greatest current value to avoid any potential future collisions. That said, there can be time when you actually which to set the seed to a Gap in an existing sequence, the function allows this and you would do something like:

? ResetAutoIncrementCounter("TableName", "FieldName", 300, , False)

The Importance Of Sequential Numbering In AutoNumber Fields

I think it is very important to briefly talk about sequential numbering in AutoNumber fields.

When we are talking about setting the counter in such a manner, 99% of the time we are talking about Primary Key (PK) fields. The thing to understand here is that a PK has one purpose, and only one purpose. That is to uniquely identify a record (a row of data). Most of the time, PK values shouldn’t even be displayed to the user as it isn’t a number for them, it is a number for the system only!

As a developer, you should not care at all if there are gaps, that a number doesn’t immediately follow the previous entries value.

If you need to have a sequential numbering value that you can rely on, that is important to the end-user, say an [Employee Number], [Order Number], [Invoice Number], … then you should be creating a separate numeric field that you populate yourself in real-time (using something like Nz(DMax(…)), 0) + 1) that you have full control over.
 

Useful Resources

Introduction to data types and field properties - Microsoft Support

Overview of data types and field properties in Access, and detailed data type reference. including Memo, Date/Time, and Text.

Modify a table’s design using Access SQL

After you have created and populated a table, you may need to modify the table's design. To do so, use the ALTER TABLE statement...

ALTER TABLE statement (Microsoft Access SQL)

Modifies the design of a table after it has been created with the CREATE TABLE statement.

SQL ALTER COLUMN

The ALTER COLUMN command is used to change the data type of a column in a table.