Access: Overwrite AutoNumber value-CodePudding

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?

CodePudding user response：

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

CodePudding user response：

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')]