最后插入行的自动编号值 - MS Access / VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1628267/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Autonumber value of last inserted row - MS Access / VBA
提问by a_m0d
I have a JETtable 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:
我有一个JET以自动编号为主键的表，我想知道如何在插入行后检索此编号。我曾想过使用MAX()检索具有最高值的行，但不确定这有多可靠。一些示例代码：
Dim query As String
Dim newRow As Integer
query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
newRow = CurrentDb.Execute(query)
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.
现在我知道这行不通，因为Execute()不会返回主键的值，但这基本上是我正在寻找的那种代码。我需要使用新行的主键来更新另一个表中的许多行。
What would be the simplest / most readable way of doing this?
这样做的最简单/最易读的方法是什么？
回答by David-W-Fenton
In your example, because you use CurrentDB to execute your INSERT you've made it harder for yourself. Instead, this will work:
在您的示例中，因为您使用 CurrentDB 来执行您的 INSERT，所以您自己变得更难了。相反，这将起作用：
Dim query As String
Dim newRow As Long ' note change of data type
Dim db As DAO.Database
query = "INSERT INTO InvoiceNumbers (date) VALUES (" & NOW() & ");"
Set db = CurrentDB
db.Execute(query)
newRow = db.OpenRecordset("SELECT @@IDENTITY")(0)
Set db = Nothing
I used to do INSERTs by opening an AddOnlyrecordset and picking up the ID from there, but this here is a lot more efficient. And note that it doesn't require ADO.
我过去常常通过打开一个AddOnly记录集并从那里获取 ID来执行 INSERT ，但这里的效率要高得多。请注意，它不需要ADO.
回答by Tony Toews
If DAOuse
如果DAO使用
RS.Move 0, RS.LastModified
lngID = RS!AutoNumberFieldName
If ADOuse
如果ADO使用
cn.Execute "INSERT INTO TheTable.....", , adCmdText + adExecuteNoRecords
Set rs = cn.Execute("SELECT @@Identity", , adCmdText)
Debug.Print rs.Fields(0).Value
cnbeing a valid ADO connection, @@Identitywill return the last
Identity(Autonumber) inserted on this connection.
cn作为有效的 ADO 连接，@@Identity将返回Identity在此连接上插入的最后一个
（自动编号）。
Note that @@Identitymight be troublesome because the last generated value may not be the one you are interested in. For the Access database engine, consider a VIEWthat joins two tables, both of which have the IDENTITYproperty, and you INSERT INTOthe VIEW. For SQL Server, consider if there are triggers that in turn insert records into another table that also has the IDENTITYproperty.
请注意，这@@Identity可能会很麻烦，因为最后生成的值可能不是您感兴趣的值。对于 Access 数据库引擎，请考虑VIEW连接两个表的a ，这两个表都具有IDENTITY属性，而您INSERT INTO的VIEW. 对于 SQL Server，请考虑是否有触发器依次将记录插入到另一个也具有该IDENTITY属性的表中。
BTW DMaxwould not work as if someone else inserts a record just after you've inserted one but before your Dmaxfunction finishes excecuting, then you would get their record.
顺便说一句DMax，就像其他人在您插入记录之后但在您的Dmax函数完成执行之前插入记录一样，您将获得他们的记录。
回答by LeCygne
This is an adaptation from my code for you. I was inspired from developpez.com(Look in the page for : "Pour insérer des données, vaut-il mieux passer par un RecordSet ou par une requête de type INSERT ?"). They explain (with a little French). This way is much faster than the one upper. In the example, this way was 37 times faster. Try it.
这是我为您编写的代码的改编版。我的灵感来自于developpez.com（在页面中查找：“ Pour insérer des données, vaut-il mieux passer par un RecordSet ou par une requête de type INSERT？”）。他们解释（用一点法语）。这种方式比一种鞋面快得多。在这个例子中，这种方式快了 37 倍。尝试一下。
Const tableName As String = "InvoiceNumbers"
Const columnIdName As String = "??"
Const columnDateName As String = "date"
Dim rsTable As DAO.recordSet
Dim recordId as long
Set rsTable = CurrentDb.OpenRecordset(tableName)
Call rsTable .AddNew
recordId = CLng(rsTable (columnIdName)) ' Save your Id in a variable
rsTable (columnDateName) = Now() ' Store your data
rsTable .Update
recordSet.Close
LeCygne
莱西涅
回答by Derek Ebrey
Private Function addInsert(Media As String, pagesOut As Integer) As Long
Set rst = db.OpenRecordset("tblenccomponent")
With rst
.AddNew
!LeafletCode = LeafletCode
!LeafletName = LeafletName
!UNCPath = "somePath\" + LeafletCode + ".xml"
!Media = Media
!CustomerID = cboCustomerID.Column(0)
!PagesIn = PagesIn
!pagesOut = pagesOut
addInsert = CLng(rst!enclosureID) 'ID is passed back to calling routine
.Update
End With
rst.Close
End Function
回答by RMittelman
Both of the examples immediately above didn't work for me. Opening a recordset on the table and adding a record does work to add the record, except:
上面的两个例子都对我不起作用。在表上打开记录集并添加记录确实可以添加记录，除了：
myLong = CLng(rs!AutoNumberField)
returns Null if put between rs.AddNew and rs.Update. If put after rs.Update, it does return something, but it's always wrong, and always the same incorrect value. Looking at the table directly after adding the new record shows an autonumber field value different than the one returned by the above statement.
如果放在 rs.AddNew 和 rs.Update 之间，则返回 Null。如果放在 rs.Update 之后，它确实返回了一些东西，但它总是错误的，并且总是相同的错误值。添加新记录后直接查看表会显示与上述语句返回的值不同的自动编号字段值。
myLong = DLookup("AutoNumberField","TableName","SomeCriteria")
will work properly, as long as it's done after rs.Update, and there are any other fields which can uniquely identify the record.
将正常工作，只要在 rs.Update 之后完成，并且有任何其他字段可以唯一标识记录。
