检索 Access 表的下一个自动编号 [英] Retrieve next AutoNumber for Access table

问题描述

我在 Microsoft Access/JET 中有一个表，它有一个自动编号字段，该字段以增量方式设置，用作表的主键.我需要知道下一条插入记录的主键值是什么，但我需要在插入记录之前知道该值.使用 SELECT MAX([ID]) + 1 FROM [TableName]; 将不起作用，因为记录通常会从表的末尾删除.(插入新记录只是为了找出值也不是一种选择.)

I have a table in Microsoft Access/JET that has an AutoNumber field that's set incrementally which serves as the table's primary key. I need to know what the value of the primary key will be for the next inserted record, but I need to know the value before the record is inserted. Using SELECT MAX([ID]) + 1 FROM [TableName]; will not work because records are routinely deleted from the end of the table. (Inserting a new record just to figure out the value is not an option either.)

我知道使用 显示表格状态 命令.有什么可以让我使用 ADO、DAO、VB6 或任何其他可用工具为 Access/JET 做同样的事情吗?

I know that this is easily done in MySQL by using the SHOW TABLE STATUS command. Is there anything that will let me do this exact same thing for Access/JET using ADO, DAO, VB6 or any other available tools?

推荐答案

您可以使用 ADOX(Microsoft ADO Extensions for DDL and Security)来确定自动编号字段的当前 种子" 值.>

You can use ADOX (Microsoft ADO Extensions for DDL and Security) to determine your autonumber field's current "Seed" value.

Public Function NextAutonumber(ByVal pTable As String, _
        ByVal pAutonumField As String) As Long

    Dim cat As Object
    Set cat = CreateObject("ADOX.Catalog")
    Set cat.ActiveConnection = CurrentProject.Connection
    NextAutonumber = cat.Tables(pTable).Columns(pAutonumField).Properties("Seed")
    Set cat = Nothing
End Function

请注意，在多用户情况下，这种方法可能会给出错误的结果……如果另一个用户可以在您检索下一个自动编号和您实际执行 INSERT>插入.如果它很重要，您可以通过在 INSERT 之后检查 SELECT @@Identity 来验证您是否获得了预期的值.

Note this approach could give the wrong result in a multi-user situation ... if another user can sneak an INSERT in between the time you retrieve the next autonumber and you actually do your INSERT. If it's critical, you could verify whether you got the value you expected by checking SELECT @@Identity after the INSERT.

这篇关于检索 Access 表的下一个自动编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！