Does @@IDENTITY work safely when used by different users at the same time, if not are there alternatives?

I have written an Excel VBA macro to insert some information into a log table:

Function Changelog(File As String) As Integer
    Dim person As String
    Dim sql As String
    Dim command As ADODB.command
    Dim rs As ADODB.Recordset
    Dim p_person As ADODB.Parameter
    Dim p_file As ADODB.Parameter

    person = Environ("USERNAME")
    sql = "INSERT INTO changelog (changelog_person, changelog_file) VALUES (?,?)"
    Set command = CreateObject("ADODB.command")

    ' Create SQL command with parameters and run it
    command.ActiveConnection = Globals.AdoConnection
    command.CommandText = sql
    Set p_person = command.CreateParameter("person", Type:=adVarWChar, Size:=LenB(person), Value:=person)
    Set p_file = command.CreateParameter("file", Type:=adVarWChar, Size:=LenB(File), Value:=File)
    command.Parameters.Append p_person
    command.Parameters.Append p_file
    command.Execute

    ' Get the ID of the just inserted row from the db
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open "SELECT @@IDENTITY", Globals.AdoConnection
    Changelog = rs(0)
    rs.Close
End Function

Open in new window


My question regards the use of 2 separate calls to the database and the possibility of concurrency issues. The macro is probably not going to be heavily used, but I want to know whether it is absolutely safe to assume that in this example “SELECT @@IDENTITY” will always give the ID of the inserted row from above, even if another user opens the same Excel file and runs the macro at the exact same time.

I’m asking because there is a TSQL function called SCOPE_IDENTITY() which is similar to @@IDENTITY and is supposed to be limited to the current scope, but I haven’t had any success trying to use it from the VBA macro.

On the other hand, maybe @@IDENTITY will work safely within the current session / Adodb.Connection ?
HelpdeskJBCAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess>
Yeah the second call will not give you the identity of the first call.  
Better to throw both in the same SQL, then open the recordset, which will both process the INSERT and return the identity.
0
Scott PletcherSenior DBACommented:
Neither @@IDENTITY nor SCOPE_IDENTITY() is 100% in that environment.

Best would be the use OUTPUT clause of the INSERT statement to return the actual value inserted into the table.

Unfortunately, I'm not exactly sure how to return the result of the OUTPUT clause to VBA.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
@@identity has the issue that if you insert into a table with trigger where the trigger inserts into another (log) table also with identity, you will get that other table's identity ...
scope_identity() does not have that issue, so it would actually work, even if you do 2 calls.

however, to avoid the 2nd roundtrip to the db, you can do it in 1 single call:
Function Changelog(File As String) As Integer
    Dim person As String
    Dim sql As String
    Dim command As ADODB.command
    Dim rs As ADODB.Recordset
    Dim p_person As ADODB.Parameter
    Dim p_file As ADODB.Parameter

    person = Environ("USERNAME")
    sql = "SET NOCOUNT ON ; INSERT INTO changelog (changelog_person, changelog_file) VALUES (?,?); SELECT SCOPE_IDENTITY() id "
    Set command = CreateObject("ADODB.command")

    ' Create SQL command with parameters and run it
    command.ActiveConnection = Globals.AdoConnection
    command.CommandText = sql
    Set p_person = command.CreateParameter("person", Type:=adVarWChar, Size:=LenB(person), Value:=person)
    Set p_file = command.CreateParameter("file", Type:=adVarWChar, Size:=LenB(File), Value:=File)
    command.Parameters.Append p_person
    command.Parameters.Append p_file
    set rs = command.Execute

    if rs.eof and rs.bof then ....
      'ooops 
       Changelog = 0
    else
       Changelog = rs("id").Value
    end if
    rs.Close
    set rs = nothing
    set command = nothing
End Function 

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Jacques Bourgeois (James Burger)PresidentCommented:
You are using ADODB with Excel, meaning that this code uses the Access Database Engine to query an Excel sheet. Forget almost everything that you can read about TSQL . Things like OUTPUT parameters, SCOPE_IDENTITY() and triggers do not exist in such an environment.

I would also be very surprised if @@IDENTITY itself would work. It is supposed to return the value of the AutoNumber (in Access) or Identity (in SQL Server) generated by the last INSERT. Unless somebody worked very hard to hide from the fact that the notion of an autonumeric field exists in Excel, @@IDENTITY won't work there.

And if by some chance it does, I would not care about multiusers crossover. First of all, @@IDENTITY has a connection Scope. Since the 2 users would have different connections, they would both have their own @@IDENTITY. Second, unless you changed the properties of the sheet, the Sheet is locked while it is opened by one user, so it is impossible for a second user to write in the sheet while the first INSERT is happening.

Personnally, when I work with Excel, I would rather speak directly to Excel than going through SQL. It then offers me all the power of Excel, not the limited SQL that is provided in that situation. See http://www.thescarms.com/dotnet/ExcelObject.aspx for a basic rundown of how to speak directly to Excel.
0
Guy Hengel [angelIII / a3]Billing EngineerCommented:
JamesBurger,

  I am a little bit surprised by your statements ...
  for me, the ADODB used here is against a sql server db, and not against excel.
    => this may not be a correct assumption from my side, to be clarified by the asker,  

  hence, if it's against sql server, @@identity and scope_identity will work just fine (I use that every day ...)

  I confirm though that @@identity is available if you query against ms access via jet, and scope_identity not being available in that case.

my 3cents
0
Jacques Bourgeois (James Burger)PresidentCommented:
@angelIII

You might be right. I was skewed by seeing "Excel" in the following sentence in the original question. But it might well be that the macro is in Excel and that it is calling SQL Server underneath.

even if another user opens the same Excel file and runs the macro at the exact same time

If so, then definitively, there is no problem, because @@IDENTITY is scoped to the connection.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.