SELECT @@IDENTITY not scoped by DB object?

Question

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

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

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

score 9 · Accepted Answer · answered May 09 '11 at 21:45

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

This outputs the following:

1    2
1    2    2

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

Wow, that's interesting. I would have guessed that it was by actual database, and since database variables initialized with CurrentDB are always the same database, they'd both return the same thing. Good work! +1 — David-W-Fenton, May 12 '11 at 01:58

score 0 · Answer 2 · answered May 09 '11 at 21:45

0

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

Although it is .Net code, the key is the fact that @@Identity is connection specific, and as you are using CurrentDb in both cases, the same connection will be being used.

answered May 09 '11 at 21:45

Duncan Howe

2,965
19
18

I'm not using connections. This is DAO code run from inside MS Access operating on a local table. – mwolfe02 May 09 '11 at 21:46
CurrentDB is not a connection object -- it's a structure pointing to the database currently open in the Access UI. – David-W-Fenton May 12 '11 at 01:59

SELECT @@IDENTITY not scoped by DB object?

2 Answers2

Linked