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

Question

这是一个

非常常见的问题，但我无法获取上次插入记录的 ID。我正在使用 DAO 和 ODBC 链接表来复制记录及其子记录。我的表位于 SQL Server 2008 中，并且具有 ID 字段的标识字段。

这是我到目前为止尝试过的。我在这里的第一段代码导致错误 3167，记录被删除。如果我进行调试。打印记录集实际上包含 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

这是我尝试的下一件事：

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

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

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

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

编辑1：
我现在使用以下代码，它正在做我需要/想要它做的事情。我怀疑这与使用 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")

Answer 1

据

我所知@@IDENTITY不适用于基于光标的插入。DAO 和 ADO 都在后台使用光标。

.Update记录后，只需读取值即可取回标识值。

通过使用键集语义打开的 ADO 记录集，以下内容对我来说效果很好：

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

通过用 Dynaset 语义打开的 DAO 记录集，以下内容对我来说效果很好：

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

您应该避免.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")

Answer 2

以下内容按预期工作（使用 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

关键点：而不是使用"选择前 2 名"或"选择前 1 名"等。我使用了'dbSeeChanges or dbAppendOnly'。我在 sql 探查器中验证了打开记录集不会生成对 SQL Server 的任何查询。

发出更新时，Access 会生成一个插入语句，后跟一个 SELECT @@IDENTITY，以获取新记录的 id。

已编辑：添加缺失的 .添加新，删除重复的。更新。

Answer 3

它不能与SQL Server Backend一起使用（在多用户应用程序中）).对于访问表，它工作对于 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

然后在前端使用此 SP

Answer 4

只需在执行 Update 语句之前获取键字段的值。 如下面的评论所述，如果您使用的是与 Microsoft Access 不同的后端，则此过程将不起作用。 但是我把这个回复留在这里，以防这是你的用例，你只是在寻找如何获得最后插入记录的ID的一般问题的答案。

例如，您可以使用 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