将任意值插入AutoNumber列

Question

我有一个访问数据库。我的前端依赖于一个表中的特定记录。ID列必须具有值1。

但是该列是AutoNumber类型的。我不能用特定的ID创建记录。

我尝试了各种方法，从删除关系、键和将数据类型更改为正常数字类型。

但是，即使创建了ID=1记录，也不能将列数据类型更改为AutoNumber。

这有可能吗？

Answer 1

但是该列是AutoNumber类型的。我无法创建带有特定ID的记录。

其实那不是真的。Access数据库引擎允许我们在AutoNumber字段中插入任意值。下面是Python，但它说明了这一点：

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

编辑：

感谢@Storax提到，如果我们将n插入表中，则Access数据库引擎会将下一个AutoNumber值重置为n+1。如果n大于现有的最大AutoNumber值，这是可以的，但如果“回填”值(如id=1 )已经存在，则会导致错误。

正确重置下一个AutoNumber值的最简单方法是在MSACCESS.EXE中打开数据库，并在其上运行“”操作。

Answer 2

如果您能够删除关系，并且没有其他表依赖于ID，则可以使用以下代码重置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

为了安全起见，在运行这段代码之前，请自己承担风险并备份数据库。

如果你的自动编号有意义，那么你迟早会遇到麻烦。

进一步的阅读.在这里，您还可以找到一个说明，说明在其他表中有相关记录时，如何继续操作。

更新： Gord的方法在本例中是更好的方法。在appaoach中唯一的步骤是将AutoId字段重置为正确的字段，否则访问引擎将使用2作为下一个自动确定的AutoID。下面的代码将执行以下操作

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