在多用户环境中使用@@ IDENTITY [英] Using @@IDENTITY in a multi user environment

问题描述

Access VBA中的 SELECT @@ IDENTITY 语句将允许用户查看/获取在表中生成的 AutoNumber ，对于该表，记录刚刚被插入到数据库中.如果只有一个用户并且在任何时候仅发生一次INSERT，则这在某种程度上很容易跟踪和维护.

The SELECT @@IDENTITY statement in Access VBA, would allow one to view/obtain the AutoNumber that has been generated in a table, for which a record has just been inserted, on the database. This is somewhat easy to track and maintain, if there is just one user and only one INSERT takes place at any moment of time.

我的应用程序是多用户，到目前为止，只有一个人用于进行数据输入.现在我们有两个人，当一个用户(布朗先生)插入一条记录(自动ID:1234)，而一秒钟(我们正在处理毫秒)时，另一个用户(格林先生)插入一条记录(自动ID:1235) )，@@ IDENTITY似乎返回布朗先生的最新ID 1235，而不是他插入的1234.格林先生得到的是相同的1235，这是正确的.

My application is multi user, so far only one person used to do data entry. Now we have two people and when one user (Mr Brown) inserts a record (Auto ID : 1234) and within a second (we are dealing with milliseconds) another (Mr Green) INSERTS a record (Auto ID : 1235), the @@IDENTITY seems to return the latest ID 1235 for Mr. Brown and not 1234 which he inserted. As the same Mr. Green gets the same 1235, which is correct.

我的问题是，有一种方法可以确保@@ IDENTITY为执行INSERT的用户返回该特定INSERT的自动ID.这是我的代码，如果有帮助的话.

My Question is, is there a way to make sure that @@IDENTITY return the AutoID of that particular INSERT for that user that performed the INSERT. This is my code, if that helps.

tranDB As DAO.Database
Set tranDB = CurrentDb
tranSQL = "INSERT INTO Transactions (Password....." 'My Insert goes here

tranDB.Execute tranSQL

Dim idRS As DAO.Recordset
Set idRS = tranDB.OpenRecordset("SELECT @@IDENTITY AS LastID;")
TranID = idRS!LastID
idRS.Close
Set idRS = Nothing

编辑:不是 MS Access的副本TableAdapter插入后获取身份.由于涉及SQL Server的另一个线程，我的是JET Engine而不是.NET，我在VBA中使用此代码.

EDIT: Not a Duplicate of MS Access TableAdapter Get Identity after insert. As the other thread involves with SQL Server, mine is JET Engine and Not .NET, I am using this code in VBA.

推荐答案

Paul尝试将其封装在事务中吗?试试这个

Paul have your tried to encapsulate it within a transaction? try this

    Dim tranDB As DAO.Database
    Set tranDB = CurrentDb
    tranSQL = "INSERT INTO Transactions (Password....." 'My Insert goes here

    dbEngine.BeginTrans
    on Error goto ERROR_INSERT:
        tranDB.Execute tranSQL, dbFailOnError

        Dim idRS As DAO.Recordset
        Set idRS = tranDB.OpenRecordset("SELECT @@IDENTITY AS LastID;")
        'ideally put another error trap here
        TranID = nz(idRS("LastID"),0)
    DBEngine.CommitTrans

   On Error resume Next
   idRS.Close
   Set idRS = Nothing
   SET tranDB = nothing
   Exit sub/Function

ERROR_INSERT:
    dbengine.rollback
    'and  other stuffs or resume to exit label

这篇关于在多用户环境中使用@@ IDENTITY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！