• ** There has been a recent site upgrade. Please clear your browser cache to avoid issues. **
  • New forum feature - post voting and best solution

    Check out this thread for the details: https://www.access-programmers.co.uk/forums/threads/new-forum-feature-post-voting-and-best-answer.314134/

    This new feature looks great to me! :)

  • We now have 3 forum themes

    Go for the default (light) theme, Shades of Grey or Shades of Blue. I just added the Blue one.

    The thread about it is here: https://www.access-programmers.co.uk/forums/threads/new-forum-theme-shades-of-blue.314136/

Is LAST_INSERT_ID mysql user specific? (1 Viewer)

bjsteyn

Registered User.
Local time
Today, 03:13
Joined
May 15, 2008
Messages
113
Just got a query i'm inserting records into a table the an auto counter primary key lets say its called ID.

After i insert a record i fetch the new unique ID created with SELECT last_insert_id() FROM mytable to build my audit trail and know what record to audit. That is my unique key.

What i want to know what if 2 users add a record at the same time by any chance, will the select last_insert_id fetch the right record id, by user.

Just need to make sure that won't cause a problem in the future even tho the chances of that happening is slim.
 

Minty

AWF VIP
Local time
Today, 01:13
Joined
Jul 26, 2013
Messages
7,351
As far as I'm aware access doesn't have an last_insert_id() function, so I would agree that it's a MySQL specific thing.

If you use a DAO you can use the following
Code:
Private Sub Test192674914368()
    Dim lastID As Long
    With CurrentDb
        With .CreateQueryDef("", _
            "INSERT INTO tblSchoolWorkingDays " & _
                "( CALENDAR_DATE ) " & _
            "VALUES " & _
                "( [prm0] )")
            .Parameters(0) = Date
            .Execute dbFailOnError
            .Close
        End With
        lastID = .OpenRecordset("SELECT @@IDENTITY").Fields(0)
    End With
End Sub
Blatantly stolen from here https://www.access-programmers.co.UK/forums...amp;postcount=8

It's important that you use the same database object to get the ID, in the example posted above it uses CurrentDb.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 18:13
Joined
Oct 29, 2018
Messages
12,525
Just got a query i'm inserting records into a table the an auto counter primary key lets say its called ID.

After i insert a record i fetch the new unique ID created with SELECT last_insert_id() FROM mytable to build my audit trail and know what record to audit. That is my unique key.

What i want to know what if 2 users add a record at the same time by any chance, will the select last_insert_id fetch the right record id, by user.

Just need to make sure that won't cause a problem in the future even tho the chances of that happening is slim.
Hi. I think it would, but it's just a guess. If MySQL is anything like Access, then fetching the last inserted record should be "connection" specific. And since each user would be using a separate connection, I am guessing they won't run into each other. Still, it's just a guess though...
 

Users who are viewing this thread

Top Bottom