最后插入行的自动编号值 - MS Access/VBA [英] Autonumber value of last inserted row - MS Access / VBA

问题描述

我有一个以自动编号作为主键的 JET 表，我想知道如何在插入行后检索此编号.我曾想过使用 MAX() 来检索具有最高值的行，但我不确定这有多可靠.一些示例代码:

I have a JET table with an auto-number as the primary key, and I would like to know how I can retrieve this number after inserting a row. I have thought of using MAX() to retrieve the row with the highest value, but am not sure how reliable this would be. Some sample code:

Dim query As String
Dim newRow As Integer
query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
newRow = CurrentDb.Execute(query)

现在我知道这行不通，因为 Execute() 不会返回主键的值，但这基本上是我正在寻找的那种代码.我需要使用新行的主键来更新另一个表中的许多行.

Now I know that this wouldn't work, since Execute() won't return the value of the primary key, but this is basically the kind of code I am looking for. I will need to use the primary key of the new row to update a number of rows in another table.

最简单/最易读的方法是什么?

What would be the simplest / most readable way of doing this?

推荐答案

如果DAO使用

RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName

如果ADO使用

cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value

cn 是一个有效的 ADO 连接，@@Identity 将返回最后一个身份(自动编号)插入此连接.

cn being a valid ADO connection, @@Identity will return the last Identity (Autonumber) inserted on this connection.

请注意，@@Identity 可能会很麻烦，因为最后生成的值可能不是您感兴趣的值.对于 Access 数据库引擎，请考虑一个 VIEW连接两个表，它们都具有 IDENTITY 属性，并且您INSERT INTO VIEW.对于 SQL Server，请考虑是否有触发器依次将记录插入另一个也具有 IDENTITY 属性的表中.

Note that @@Identity might be troublesome because the last generated value may not be the one you are interested in. For the Access database engine, consider a VIEW that joins two tables, both of which have the IDENTITY property, and you INSERT INTO the VIEW. For SQL Server, consider if there are triggers that in turn insert records into another table that also has the IDENTITY property.

BTW DMax 不会像其他人在您插入记录后但在您的 Dmax 函数完成执行之前插入记录一样工作，那么您将获得他们的记录.

BTW DMax would not work as if someone else inserts a record just after you've inserted one but before your Dmax function finishes excecuting, then you would get their record.

这篇关于最后插入行的自动编号值 - MS Access/VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！