From MVP Allen Browne
Function ShowIdentity() As Variant
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine(0)(0)
db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"
Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
ShowIdentity = rs!LastID
rs.Close
Set rs = Nothing
Set db = Nothing
End Function
Ok, now i found a typo. In my SQL statement i was forcing to find identity in a specific table, which was wrong:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY From tblMyTable As LastId;")
Instead, I put:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY As LastId;")
Now it works!
Nevertheless, another question arises. Is it safe method to find last inserted record? What happen if somebody will create a record in different table in the same time?
Ok, now i found a typo. In my SQL statement i was forcing to find identity in a specific table, which was wrong:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY From tblMyTable As LastId;")
Instead, I put:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY As LastId;")
Now it works!
Nevertheless, another question arises. Is it safe method to find last inserted record? What happen if somebody will create a record in different table in the same time?
Should be safe as long as you and the other user are not using the same db object, which should be unlikely if you're using a properly split configuration.
Ok, now i found a typo. In my SQL statement i was forcing to find identity in a specific table, which was wrong:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY From tblMyTable As LastId;")
Instead, I put:
Code:
Set rs = db.OpenRecordset("Select @@IDENTITY As LastId;")
Now it works!
Nevertheless, another question arises. Is it safe method to find last inserted record? What happen if somebody will create a record in different table in the same time?
It is guaranteed to be the ID of the record inserted in the session you are in by using the set db object, that is the whole point of using it, assuming you need to do something with ID inserted by the Insert statement you ran locally.
If someone else inserts a record, they would get the record ID they inserted.
Using DMax() would only return the last record inserted by anyone using the system, so it isn't necessarily going to be the record inserted by the local user.
Which you use depends on the scenario you are trying to process?