Go Back   Access World Forums > Apps and Windows > VB.NET

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 04-09-2019, 12:51 AM   #1
bjsteyn
Newly Registered User
 
Join Date: May 2008
Posts: 111
Thanks: 1
Thanked 9 Times in 9 Posts
bjsteyn is on a distinguished road
Is LAST_INSERT_ID mysql user specific?

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.

bjsteyn is offline   Reply With Quote
Old 04-09-2019, 01:38 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,268
Thanks: 148
Thanked 1,683 Times in 1,655 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Is LAST_INSERT_ID mysql user specific?

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...mp;postcount=8

It's important that you use the same database object to get the ID, in the example posted above it uses CurrentDb.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 04-09-2019, 07:02 AM   #3
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 2,590
Thanks: 29
Thanked 636 Times in 619 Posts
theDBguy will become famous soon enough theDBguy will become famous soon enough
Re: Is LAST_INSERT_ID mysql user specific?

Quote:
Originally Posted by bjsteyn View Post
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...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
i want to allow user to download query data from mySQL server debywilliams SQL Server 2 09-21-2016 06:32 AM
End-User Specific Query aroth Queries 1 07-11-2008 11:57 AM
User-Specific Security GailFialho Excel 2 11-13-2007 01:43 PM
Query by specific user Help sysop470 Queries 2 10-16-2007 10:13 AM
User Specific Report jempie Reports 1 02-22-2005 11:51 AM




All times are GMT -8. The time now is 08:52 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World