SELECT @@IDENTITY 不受数据库对象限制？

Question

我在 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

我希望它输出以下内容（即，每个不同的数据库对象都有自己的“最新身份”值）：

1     2
1     2    0

相反，我得到（即，它似乎是全局范围的）：

1     2
2     2    2

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

Answer 1

事实证明，SELECT @@IDENTITY 的作用域是由会话确定的。在 ADO 中，这是通过连接来处理的。在DAO中，我们必须使用工作区来隔离范围。以下代码按预期工作：

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，但这很容易编写代码。

Answer 2

请参阅http://www. mikesdotnetting.com/Article/54/Getting-the-identity-of-the-most-recently-added-record

虽然是.Net代码，但关键是@@Identity是连接特定，并且当您在两种情况下都使用 CurrentDb 时，将使用相同的连接。