Insert... ; Select @@identity (1 Viewer)

24sharon

Registered User.
Local time
Today, 12:27
Joined
Oct 5, 2004
Messages
147
I work with project access (*.adp)

I want to know if it possible to take the last ID after the sql INSERT QUERY

my code:
Code:
strSQL = "INSERT INTO missionFather ....."
DoCmd.RunSQL strSQL

thanks!
 

RuralGuy

AWF VIP
Local time
Today, 13:27
Joined
Jul 2, 2005
Messages
13,826
Code:
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
 

emimarz

New member
Local time
Today, 16:27
Joined
Mar 18, 2010
Messages
1
hi
i do it and allways return 0
i cant understand
 

gringope24

Member
Local time
Today, 20:27
Joined
Apr 1, 2020
Messages
51
I am facing the same problem as emimarz - rs always return 0. Has anybody figured it out?
 

Gasman

Enthusiastic Amateur
Local time
Today, 19:27
Joined
Sep 21, 2011
Messages
14,046
Works for me on a linked Access BE ?
 

gringope24

Member
Local time
Today, 20:27
Joined
Apr 1, 2020
Messages
51
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?
 

bastanu

AWF VIP
Local time
Today, 12:27
Joined
Apr 13, 2010
Messages
1,401
Maybe I'm missing something but why not just simply dMax("ID","missionFather ") where ID is the identity field?
Cheers,
 

theDBguy

I’m here to help
Staff member
Local time
Today, 12:27
Joined
Oct 29, 2018
Messages
21,358
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.
 

Minty

AWF VIP
Local time
Today, 19:27
Joined
Jul 26, 2013
Messages
10,355
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?
 

gringope24

Member
Local time
Today, 20:27
Joined
Apr 1, 2020
Messages
51
What should I understand as a session? Does below code open a session?
Code:
Dim db As Database
Set db = CurrentDB

Does below code open two sessions?
Code:
Dim db1, db2 As Database
Set db1 = CurrentDB
Set db2 = CurrentDB
 

Users who are viewing this thread

Top Bottom