sqlms-accessauto-incrementautonumber

Change Access autonumber value

I have a legacy Access database. The VB code in it needs a special record with the autonumber ID value 1 in a specific table.

The table in question has a primary key as autonumber. That column is also referenced in other tables as foreign key. That is why I cannot delete or modify the primary key of the table.

I tried to reset the counter with

ALTER TABLE myTable ALTER COLUMN tId COUNTER(1,1)

But it says I cannot change the table. And I cannot set the column value manually.

How can I put a specific value in an existing primary key autonumber column?

Solution

  • You can use ADOX to change the seed of an autonumber field in Access:

    Public Sub SetAutonumber(TableName As String, ColumnName As String, Seed As Long)
    Dim c As Object 'ADOX.Catalog
    Set c = CreateObject("ADOX.Catalog")
    Set c.ActiveConnection = CurrentProject.Connection
    c.Tables(TableName).Columns(ColumnName).Properties("Seed") = Seed
End Sub

    Invoke it using the immediate window:

    SetAutonumber "MyTable", "tId", 1

    And remember to reset it:

    SetAutonumber "MyTable", "tId", DMAX("tId", "MyTable") + 1

    Note, though, that Access allows inserts into the autonumber field. It's only read-only through the GUI, but queries and VBA are allowed to change it.

    INSERT INTO myTable (tId) VALUES(1)

    That might bypass the need to set and then reset the seed.

    Also note: negative autonumbers are allowed in Access (autonumber fields are always signed), but never occur without manually setting the seed/setting them/corruption