Insert arbitrary value into an AutoNumber column

Question

I have an Access database. My frontend depends on a specific record in one table. The ID column must have the value 1.

But the column is of type AutoNumber. I cannot create a record with a specific ID.

I tried everything from removing relations, keys and changing the data type to normal number type.

But even if I then create a record of ID=1 I cannot change back the column data type to AutoNumber.

Is this even possible somehow?

Answer 1

But the column is of type AutoNumber. I cannot create a record with a specific ID.

Actually that is not true. The Access Database Engine allows us to insert arbitrary values into an AutoNumber field. The following is Python, but it illustrates the point:

table_name = "so71884564"

if crsr.tables(table_name).fetchone():  # does table exist?
    crsr.execute(f"DROP TABLE {table_name}")
crsr.execute(f"CREATE TABLE {table_name} (id COUNTER PRIMARY KEY, txt TEXT(50))")

# insert arbitrary id
crsr.execute(f"INSERT INTO {table_name} (id, txt) VALUES (2, 'bar')")
print(crsr.execute(f"SELECT * FROM {table_name}").fetchall())
# [(2, 'bar')]

# let the Access Database Engine autonumber this one
crsr.execute(f"INSERT INTO {table_name} (txt) VALUES ('baz')")
print(crsr.execute(f"SELECT * FROM {table_name}").fetchall())
# [(2, 'bar'), (3, 'baz')]

# insert the missing row for id=1
crsr.execute(f"INSERT INTO {table_name} (id, txt) VALUES (1, 'foo')")
print(crsr.execute(f"SELECT * FROM {table_name}").fetchall())
# [(2, 'bar'), (3, 'baz'), (1, 'foo')]

Edit:

Thanks to @Storax for mentioning that the Access Database Engine resets the next AutoNumber value to n + 1 if we insert n into the table. That is okay if n is greater than the largest existing AutoNumber value, but it can cause errors if we "back-fill" values like id=1 when id=2 already exists.

The easiest way to properly reset the next AutoNumber value is to open the database in MSACCESS.EXE and run a "Compact and Repair Database" operation on it.

Answer 2

If you are able to delete the relations and no other tables rely on the ID you can use the following code to reset the ID.

Sub specificRecord()

    Dim db As Database
    Set db = CurrentDb
    
    'Export/Copy data in another table
    Dim sSQL As String
    sSQL = "SELECT * INTO tblCopy FROM tblSource;"   ' Make sure tblCopy does not exist otherwise you get an error
    db.Execute sSQL

    'Delete data in source table
    sSQL = "DELETE * FROM tblSource"
    db.Execute sSQL

    'Reset ID to 1 (Modify the ID field accordingly!)
    sSQL = "ALTER TABLE tblSource ALTER COLUMN ID COUNTER(1,1);"
    db.Execute sSQL
    
    ' Import specific record into table
    ' You need to modify this step you as I do not know the field names
    ' And you have to leave out the ID field
    
    ' INSERT INTO table_name (column1, column2, column3, ...)
    ' VALUES (value1, value2, value3, ...);
    sSQL = "INSERT INTO tblSource (Test,Datum) VALUES ('xxx','01.05.2022');"
    db.Execute sSQL

    ' Import/Copy data from backup table to source table
    ' INSERT INTO tblSource column1, column1, column3, ...)
    ' SELECT tblCopy.column1, tblCopy.column1
    ' FROM tblCopy;
    
    ' see above, again leave out the ID field
    sSQL = "INSERT INTO tblSource (Test, Datum) SELECT tblCopy.Test, tblCopy.Datum FROM tblCopy;"
    db.Execute sSQL

    ' Application.RefreshDatabaseWindow
End Sub

Use at your own risk and make a backup of your database before running this code, just to be on the safe side.

If your autonumber has a meaning then you will get into trouble sooner or later.

Further reading. There you also find a description how to proceed in case you have related records in other tables

Update: Gord Thompson's approach is the better one in this case. The only step remaing in that appaoach is to reset the AutoID field to the correct one otherwise the access engine will use 2 as the next automatically determined AutoId. The following code will do this

Sub resetMaxID()

    Dim db As Database
    Dim rs As Recordset
    Dim sSQL As String
    
    Set db = CurrentDb
    sSQL = "SELECT MAX(ID) FROM tblSource"
    Set rs = db.OpenRecordset(sSQL)
    
    Dim maxID As Long
    maxID = rs.Fields(0).Value  ' This is the max AutoId
    rs.Close
    
    'Reset ID to maxId +1
    sSQL = "ALTER TABLE tblSource ALTER COLUMN ID COUNTER(" & maxID + 1 & ",1);"
    db.Execute sSQL

End Sub