Insert arbitrary value into an AutoNumber column

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?

Solution

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.