最后插入的行的自动编号值-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将返回最后一个 在该连接上插入了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屋！