SELECT @@IDENTITY 不受 DB 对象的限制? [英] SELECT @@IDENTITY not scoped by DB object?

问题描述

我在 MS Access 中有以下代码:

I have the following code in MS Access:

Sub IdentityFail()
Dim db1 As DAO.Database, db2 As DAO.Database
Dim id1 As Long, id2 As Long

    CurrentDb.Execute "CREATE TABLE LocalDummy (Col1 AUTOINCREMENT, Col2 INT)", dbFailOnError
    Set db1 = CurrentDb
    Set db2 = CurrentDb
    db1.Execute "INSERT INTO LocalDummy(Col2) VALUES(Null)", dbFailOnError
    id1 = db1.OpenRecordset("SELECT @@IDENTITY")(0)
    db2.Execute "INSERT INTO LocalDummy(Col2) VALUES(Null)", dbFailOnError
    id2 = db2.OpenRecordset("SELECT @@IDENTITY")(0)

    Debug.Print id1, id2
    Debug.Print db1.OpenRecordset("SELECT @@IDENTITY")(0), _
                db2.OpenRecordset("SELECT @@IDENTITY")(0), _
                CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)
End Sub

我希望这会输出以下内容(即，每个不同的 db 对象都有自己的最新身份"值):

I would expect this to output the following (ie, each distinct db object would have its own "most recent identity" value):

1     2
1     2    0

相反，我得到了(即，它似乎是全局范围的):

Instead I get (ie, it appears to be globally scoped):

1     2
2     2    2

我认为 SELECT @@IDENTITY 是在 Jet 4.0+ 中获取最新自动编号 ID 的安全方法.我究竟做错了什么?

I thought SELECT @@IDENTITY was the safe way to get the latest autonumber ID in Jet 4.0+. What am I doing wrong?

推荐答案

原来 SELECT @@IDENTITY 是由 session 限定的.在 ADO 中，这是通过连接处理的.在 DAO 中，我们必须使用 Workspaces 来隔离范围.以下代码按预期工作:

Turns out that SELECT @@IDENTITY is scoped by session. In ADO, this is handled via the connection. In DAO, we have to use Workspaces to isolate the scope. The following code works as expected:

Sub IdentitySucceed()
Dim ws1 As DAO.Workspace, ws2 As DAO.Workspace
Dim db1 As DAO.Database, db2 As DAO.Database
Dim id1 As Long, id2 As Long, DbPath As String

    CurrentDb.Execute "CREATE TABLE LocalDummy (Col1 AUTOINCREMENT, Col2 INT)", dbFailOnError
    'The workspace names need not be unique;'
    '  we'll use the objects themselves (ws1 and ws2) to keep them straight'
    Set ws1 = DAO.CreateWorkspace("TempWS", "Admin", "")
    Set ws2 = DAO.CreateWorkspace("TempWS", "Admin", "")
    DbPath = Application.CurrentProject.Path & "" & _
             Application.CurrentProject.Name
    Set db1 = ws1.OpenDatabase(DbPath)
    Set db2 = ws2.OpenDatabase(DbPath)
    db1.Execute "INSERT INTO LocalDummy(Col2) VALUES(Null)", dbFailOnError
    id1 = db1.OpenRecordset("SELECT @@IDENTITY")(0)
    db2.Execute "INSERT INTO LocalDummy(Col2) VALUES(Null)", dbFailOnError
    id2 = db2.OpenRecordset("SELECT @@IDENTITY")(0)

    Debug.Print id1, id2
    Debug.Print db1.OpenRecordset("SELECT @@IDENTITY")(0), _
                db2.OpenRecordset("SELECT @@IDENTITY")(0), _
                CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)
End Sub

输出如下:

1    2
1    2    2

CurrentDb 仍然不会返回 0，但这很容易编码.

CurrentDb still won't return 0, but that's easy enough to code around.

这篇关于SELECT @@IDENTITY 不受 DB 对象的限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！