Geek Answers Handbook

SELECT @@ IDENTITY not bound to a database object?

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 (i.e., each individual db object will have its own meaning of "last identity"):

 1 2 1 2 0

Instead, I get (i.e., it appears to be globally):

 1 2 2 2 2

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

+4
source share
2 answers

It turns out that SELECT @@IDENTITY is session limited. In ADO, this is handled through the connection. In the DAO, we must use Workspaces to highlight the area. 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

Outputs the following:

 1 2 1 2 2

CurrentDb will still not return 0, but it's easy enough for encoding.

+7
source

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

Although this is a .Net code, the key is that @@ Identity is connection specific, and since you use CurrentDb in both cases, the same connection will be used.

0
source

More articles:


All Articles