vba 获取上次插入记录的 ID - Access DAO、ODBC、SQL Server 2008 标识字段

Get ID of Last Inserted Record - Access DAO, ODBC, SQL Server 2008 Identity Field

提问by HK1

It's a very common question but I'm having trouble getting the ID of the last inserted record. I'm using DAO with ODBC linked tables to duplicate a record and it's child records. My tables are in SQL Server 2008 and have Identity fields for ID fields.

这是一个非常常见的问题，但我无法获取最后插入的记录的 ID。我将 DAO 与 ODBC 链接表一起使用来复制记录及其子记录。我的表在 SQL Server 2008 中，并且具有用于 ID 字段的标识字段。

Here's what I've tried so far. My first bit of code here results in error 3167, Record is Deleted. If I do a debug.Print the recordset actually contains 3 records.

这是我迄今为止尝试过的。我这里的第一个代码导致错误 3167，记录已删除。如果我进行 debug.Print 记录集实际上包含 3 条记录。

Dim r as DAO.Recordset, db as DAO.Database
Set db = CurrentDb
Set r = db.OpenRecordset("SELECT TOP 2 * FROM item ORDER BY DateTimeModified DESC", dbOpenDynaset, dbSeeChanges)
r.AddNew
'Set field values here
r.Update 'Completes without error
r.Bookmark = r.LastModified
Debug.Print r("ItemID") 'Error 3167, Record is deleted

Here's the next thing I tried:

这是我尝试的下一件事：

Debug.Print db.OpenRecordset("SELECT @@identity FROM item")(0)

This last one completes without any problem but the value returned is incorrect. Where the actual new ItemID is 321 this returns the value 614. The value it is returning does appear to be incremental (it changes as I keep testing this) but it does not appear to relate at all to my table. There is no field with the value 614. I've double checked to make sure I'm looking up the correct table.

最后一个完成没有任何问题，但返回的值不正确。在实际的新 ItemID 为 321 的情况下，它返回值 614。它返回的值似乎是增量的（随着我不断测试它而改变），但它似乎与我的表完全无关。没有值为 614 的字段。我已经仔细检查以确保我正在查找正确的表。

I know I could use something like DLookup or DMax but I don't think that would be considered bullet proof in a multi-user environment.

我知道我可以使用 DLookup 或 DMax 之类的东西，但我认为这在多用户环境中不会被认为是防弹的。

I suppose I could use a Stored Procedure with ADO to get around this problem. I'm wondering if that is my only option?

我想我可以使用带有 ADO 的存储过程来解决这个问题。我想知道这是我唯一的选择吗？

Edit1:
I'm now using the following code and it is doing what I need/want it to. I suspect this is basically the same as using DMax.

Edit1：
我现在使用下面的代码，它正在做我需要/想要的。我怀疑这与使用 DMax 基本相同。

Dim r as DAO.Recordset, db as DAO.Database
Set db = CurrentDb
Set r = db.OpenRecordset("SELECT TOP 1 * FROM item ORDER BY ItemID DESC", dbOpenDynaset, dbSeeChanges)
r.AddNew
'Set field values here
r.Update
r.Requery
r.MoveFirst
Debug.Print r("ItemID")

回答by ta.speot.is

As far as I'm aware @@IDENTITYdoesn't work for cursor-based inserts. DAO and ADO both use cursors behind the scenes.

据我所知@@IDENTITY，不适用于基于游标的插入。DAO 和 ADO 都在幕后使用游标。

After you .Updatethe record you should be able to get the identity value back simply by reading the value.

在您.Update记录之后，您应该能够通过读取值来获取身份值。

The following works fine for me via an ADO Recordset opened with Keyset semantics:

通过使用 Keyset 语义打开的 ADO Recordset，以下对我来说很好用：

r.Update
Debug.Print r("ItemID")

The following works fine for me via a DAO Recordset opened with Dynaset semantics:

通过使用动态集语义打开的 DAO 记录集，以下对我来说很好用：

r.Update
r.Bookmark = r.LastModified
Debug.Print r("ItemID")

You should avoid .Requeryand .MoveFirst, you're introducing concurrency problems. Consider:

你应该避免.Requery和.MoveFirst，你正在引入并发问题。考虑：

Dim r as DAO.Recordset, db as DAO.Database
Set db = CurrentDb
Set r = db.OpenRecordset("SELECT TOP 1 * FROM item ORDER BY ItemID DESC", dbOpenDynaset, dbSeeChanges)
r.AddNew
''// Set field values here
r.Update
''// At this point another user adds a new record
r.Requery
r.MoveFirst ''// ORDER BY ItemID DESC means that you're going to see the new user's row
Debug.Print r("ItemID")

回答by mendel

The following works as expected (using Office 2013 and SQL Server 2014)

以下按预期工作（使用 Office 2013 和 SQL Server 2014）

Set rsProjects = db.OpenRecordset("JobProjects", dbOpenDynaset, dbSeeChanges Or dbAppendOnly)
rsProjects.AddNew
rsProjects.Name = 'xyz'
rsProjects.Update
rsProjects.Bookmark = rsProjects.LastModified
lNewProjectID = rsProjects!ProjectID.Value

Key point: instead of using 'SELECT TOP 2' or 'SELECT TOP 1', etc. I used 'dbSeeChanges Or dbAppendOnly'. I verified in sql profiler that opening the recordset does not generate any queries to SQL Server.

关键点：我没有使用“SELECT TOP 2”或“SELECT TOP 1”等。我使用了“dbSeeChanges Or dbAppendOnly”。我在 sql profiler 中验证，打开记录集不会生成对 SQL Server 的任何查询。

When you issue the update, access generates an insert statement followed immediately by a SELECT @@IDENTITY to get the id of the new record.

当您发出更新时，访问会生成一个插入语句，紧接着是一个 SELECT @@IDENTITY 以获取新记录的 ID。

Edited: add missing .AddNew, Remove duplicate .Update.

编辑：添加缺少的 .AddNew，删除重复的 .Update。

回答by behrooz

it dosnt work with sql server backend(in multi user app ). for access table it work for sql use stored procedure.use this way

它不适用于 sql server 后端（在多用户应用程序中）。对于访问表，它适用于 sql 使用存储过程。使用这种方式

CREATE PROCEDURE dbo.AddAsset

  @Name VARCHAR(500),
  @URL  VARCHAR(2000),
  @new_identity INT = NULL OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

INSERT dbo.Assets(Name, URL) SELECT @Name, @URL;
SET @new_identity = SCOPE_IDENTITY();

END 
GO

then use this sp in front end

然后在前端使用这个 sp

回答by Bobort

Simply get the value of the key field before you execute the Updatestatement. As pointed out in the comment below, this process will not work if you are using a different backend than Microsoft Access. But I leave this response here in case that is your use case and you are just searching for an answer to the general question of how you get the ID of the last inserted record.

只需在执行Update语句之前获取键字段的值。正如下面的评论中所指出的，如果您使用的后端与 Microsoft Access 不同，则此过程将不起作用。但是，如果这是您的用例，并且您只是在寻找有关如何获取最后插入记录的 ID 的一般问题的答案，我会在此处留下此回复。

For your example, you can do this with Microsoft Access:

对于您的示例，您可以使用 Microsoft Access 执行此操作：

Dim r as DAO.Recordset, db as DAO.Database
Dim lKey As Long
Set db = CurrentDb
Set r = db.OpenRecordset("SELECT TOP 2 * FROM item ORDER BY DateTimeModified DESC", dbOpenDynaset, dbSeeChanges)
r.AddNew
    'Set field values here
    'Retrieve the key value before executing the Update
    lKey = r!ItemID
r.Update
Debug.Print lKey