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

Question

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

sql-server-2008ms-accessvbaodbc

3

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

到目前为止，我尝试过以下方法。我的第一段代码会导致错误3167，“记录已删除”。如果我进行debug.Print，则recordset实际上包含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)

这个操作可以顺利完成，但返回的值是不正确的。实际上，新项目ID是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")

- HK1

你可以使用透传查询或存储过程吗？我很确定在 SQL Server 中不应该使用 @@identity（http://wiki.lessthandot.com/index.php/6_Different_Ways_To_Get_The_Current_Identity_Value）。 - Fionnuala

我认为是 SELECT TOP 2 ... Order by DateTimeModified 引起了问题。而且 Select @@Identity 不应该与 from 子句一起使用，也不可能做你想做的事情。如果你不想使用存储过程，你可以尝试在一个批处理中执行插入和选择操作。例如：INSERT INTO ... VALUES ... ; SELECT * FROM items where ItemID = Scope_Identity()。 - Conrad Frix

@ConradFrix 我已经尝试了不同的SELECT语句来操作记录集'r'，但似乎没有任何区别。 - HK1

我曾经使用这些信息来指导我，但我猜可能不准确：http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/3507c845-66a6-4e13-8876-f6e5faae8dba - HK1

4个回答

1

以下内容符合预期（使用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

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

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

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

- mendel

1

使用两次更新，不会出现错误吗？更新时没有添加/编辑等操作吗？ - Sham Yemul

@ShamYemul 是的，那是我的代码中的一个错误，我忘记了 .AddNew，并且多了一个 .Update，我会修复我的示例代码。 - mendel

0

它不能与 SQL Server 后端（在多用户应用程序中）一起使用。对于 Access 表格，它可以工作，对于 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

然后在前端使用这个存储过程

- behrooz

-1

在执行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

- Bobort

以上方法不适用于SQL服务器，只适用于JET/ACE表。SQL服务器直到记录保存时才生成或返回自动编号PK值。您必须首先执行更新，然后最好使用书签属性将记录指针移回“lastmodified”，因为添加（仅在添加时）会导致记录指针移出您刚刚“更新”的当前记录。对于现有记录的更新，不需要使用lastmodifed书签，但对于新记录则需要。 - Albert D. Kallal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- ta.speot.is · Accepted Answer

据我所知，@@IDENTITY在基于游标的插入操作中无法正常工作。DAO和ADO都在后台使用游标。

在记录更新后，您应该能够通过读取该值来获取标识值。

以下示例对我而言可以通过使用关键集语义打开的ADO Recordset正常工作：

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

以下内容对于我来说通过使用具有Dynaset语义的DAO Recordset可以正常工作：

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")