获取上次插入记录的 ID - Access DAO、ODBC、SQL Server 2008 标识字段 [英] Get ID of Last Inserted Record - Access DAO, ODBC, SQL Server 2008 Identity Field

问题描述

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

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.

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

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.

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)

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

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.

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

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.

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

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

编辑 1:
我现在正在使用以下代码，它正在做我需要/想要的.我怀疑这与使用 DMax 基本相同.

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.

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

推荐答案

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

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

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

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

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

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

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

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

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

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

你应该避免 .Requery 和 .MoveFirst，你会引入并发问题.考虑:

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

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

这篇关于获取上次插入记录的 ID - Access DAO、ODBC、SQL Server 2008 标识字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！