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?

If the value must be 1, then don't use _AutoNumber_ but _Number_ (integer). — Gustav, Apr 15 '22 at 14:30
I can't change the whole frontend to start generating IDs instead of using the DB function for that — juergen d, Apr 15 '22 at 14:31
But _AutoNumber_ is not intended for holding specific numbers, only unique numbers. So, reconsider your design. — Gustav, Apr 15 '22 at 14:36
Exactly. It is a legacy software and a real pain. I won't change any code. I need to fix that on DB level. — juergen d, Apr 15 '22 at 14:44
So, you have one specific record which should have the ID 1, right? Second question, can you export, delete and then import all the entries ein the table in question? — Storax, Apr 15 '22 at 18:42
@smoore4: Then I would have to change the frontend code to use the new view. That could be on a lot of places. I try to avoid that. — juergen d, Apr 15 '22 at 20:47

Gord Thompson · Accepted Answer · 2022-04-16T12:28:59.280

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.

Great, it is indeed possible to create an AutoID with an arbitrary value. Even in case the AutoID is defined as a primary key you can do this as long as it does not already exist in the table. If the AutoID is not defined as a key you can insert duplicate IDs. Many Thanks for letting us know! — Storax, Apr 16 '22 at 06:32
I just found that your approach comes with a cost. If you insert a missing ID like 1with an INSERT statement then the next AutoID value will be 2 even if it is already in the table. So, in a next missing step one would need to reset the AutoID number to the correct value. But anyway, your approach is better than mine — Storax, Apr 16 '22 at 06:40

Storax · Answer 2 · 2022-04-16T06:54:57.390

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

Insert arbitrary value into an AutoNumber column

2 Answers2