@@identity

[KitaYama]

FE : Microsoft Access
BE : Sql Server

I use CurrentDb.OpenRecordset("select @@identity")(0) in AfterUpdate of a form to get the latest saved PK, without being sure how it works.

I've always been uncertain what will happen in a multi user environment.

If a user saves a new record to a table, and just after the save and before running above @@identity, another user saves another new record into the same table, does @@identity returns correct PKs for both users?
Since it is CurrentDB, and only one user is using the CurrentDB, is it safe to consider that the returned PK is correct?
I'm not sure if @@identity")(0) pulls the result from BE or CurrentDB.
I was thinking if the result is pulled out of the BE, the returned PK maybe different, because the last PK is what the other user has saved.

I hope I make sense here. I just want to be sure I don't mess anything up.
Thanks for any kind of advice.

 

[KitaYama]

While waiting, I checked ChatGPT.
According to ChatGPT, using @@identity is a disaster.

Even though CurrentDb refers to the local Access environment, the @@IDENTITY function is still being executed on the SQL Server backend. So the result is being pulled from SQL Server, where the identity values can be affected by other users, as @@IDENTITY returns the identity for the current session, not the local database context.

I was asked to use SCOPE_IDENTITY for a safer alternative, but still it can not be trusted.

Seems that there is only 2 safe ways:
1- Using Output in an insert sql statement.
2- Using a stored procedure.

Using first option means that I have to use a Insert Sql as:

INSERT INTO YourTable (Column1, Column2)
OUTPUT Inserted.YourPrimaryKey
VALUES (Value1, Value2)

I think I'll have another fight with my wife this weekend when I put all my free time on reading a little about these two solutions.

 

[Pat Hartman]

Scope_Identity is a better option but I have never been able to get it to work in Access. @@Identity is a problem if you have triggers and inserting a row in tblA can "trigger" an insert into tblB. You get the last insert, not the first. Since I don't use triggers, I have never worried about the issue.

 

[arnelgp]

you may also insert a record in a Recordset and set it's bookmark to it's lastmodified property to get the PK(identity):

Private Function fnInsertIntoSQL( _
                ByVal strLinkedTable As String, _
                ByVal strPKName As String, _
                ParamArray FieldValuePair() As Variant) _
                As Variant
    '___________________
    '
    ' note:
    '
    ' FieldValuePair()
    ' eg: "Field1", valueForField1, "Field2", ValueForField2, etc.
    '
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim i As Integer
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strLinkedTable, dbOpenDynaset, dbSeeChanges + dbFailOnError)
    With rs
        .AddNew
        For i = 0 To UBound(FieldValuePair) Step 2
            .Fields(FieldValuePair(i)) = FieldValuePair(i + 1)
        Next
        .Update
        .Bookmark = .LastModified
        fnInsertIntoSQL = .Fields(strPKName)
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Function

 

[KitaYama]

Scope_Identity is a better option but I have never been able to get it to work in Access. @@Identity is a problem if you have triggers and inserting a row in tblA can "trigger" an insert into tblB. You get the last insert, not the first. Since I don't use triggers, I have never worried about the issue.

I'm too busy now to dive into depth, but my first trials seem to prove you're correct.
Using Scope_Identity, 5 out of 5 trials failed to bring back the last PK.
I will try different variants this week end, but if you have never been able to get it work, I'm pretty sure I will never succeed.

@arnelgp Your solution means the form being unbound?

 

[KitaYama]

Just an update :

Until I come to a solid solution, I changed @@Identity, to a DMax where the filter is RegisteredBy=UserFK.

 

[arnelgp]

@arnelgp Your solution means the form being unbound?

bound or unbound, doesn't matter on the code.

 

[Minty]

@@Identity works but you need to isolate the DB instance to ensure you get the correct result.

Set db = CurrentDB
DO the insert here
db.OpenRecordset("SELECT @@IDENTITY").Fields(0)

It's certainly more reliable than DMax()

However, the second solution using

OUTPUT Inserted.YourPrimaryKey

Is definitely the most robust method, but does require some adjustment to your methodology.

 

[KitaYama]

@@Identity works but you need to isolate the DB instance to ensure you get the correct result.

Just to clarify, you mean if I set a variable for currentdb, and two different users from two different FEs, insert records into the same table at the same time, both receive the correct PKs (The PK of the record they inserted?)

thanks.

 

[Minty]

Just to clarify, you mean if I set a variable for currentdb, and two different users from two different FEs, insert records into the same table at the same time, both receive the correct PKs (The PK of the record they inserted?)

thanks.

That is my understanding.
I haven't had any need to test it for some considerable time, but do need to look at a database where it is used in the next couple of days, so I will report back.

 

[sonic8]

According to ChatGPT, using @@identity is a disaster.

[...] So the result is being pulled from SQL Server, where the identity values can be affected by other users, as @@IDENTITY returns the identity for the current session, not the local database context.

Another idiotic reply by "AI".
If @@Identity returns a value for the current session how can this be affected by other users? - It can't!
However, as others pointed out already, @@Identity returns the last generated identity value, which can be a different value than you expect if there are triggers also implicitly inserting records when you do so explicitly.

 

[cheekybuddha]

@@Identity must be called on the same connection/db - so it won't work when a new record is added via a form.

As @Minty suggests. something like:

With CurrentDb
  .Execute "INSERT INTO YourTable (...) VALUES (...)"
  Debug.Print "New ID is:", .OpenRecordest("SELECT @@IDENTITY;")(0)
End With

SCOPE_IDENTITY is not available directly in Access - you would have to use a pass-through (for the whole operation) to use it.

 

[ebs17]

in AfterUpdate of a form

In Form_BeforeUpdate, an automatically generated primary key should already be available and readable. At this point, you also have access to the correct record and its key. The measure in Form_AfterUpdate is too late and not recommended. In addition, the bound form and CurrentDb have nothing to do with each other directly. Mixing a bound form and a DAO/ADODB action is not appropriate at this point.

In order to use SELECT @@IDENTITY properly, the creation of the record and the immediate reading of the generated key would have to take place with the same instance of the database connection. CurrentDb creates a new instantiation each time and should therefore not be used in succession.

Dim oDB As DAO.Database
Set oDB = ...
oDB.Execute "INSERT INTO ..."
Debug.Print oDB.Openrecordset("SELECT @@IDENTITY")(0)

Analogous when using an ADODB connection.

 

[sonic8]

@@Identity must be called on the same connection/db - so it won't work when a new record is added via a form.

Well, it should work if you use the same DB instance:
MsgBox TheForm.Recordset.Parent.Openrecordset("SELECT @@identity").Fields(0).Value

SCOPE_IDENTITY is not available directly in Access - you would have to use a pass-through (for the whole operation) to use it.

Correct, but using a Pass-Through-Query only makes sense if you insert the new record in the very same batch as otherwise you would not be in the same scope.

 

[cheekybuddha]

Well, it should work if you use the same DB instance:
MsgBox TheForm.Recordset.Parent.Openrecordset("SELECT @@identity").Fields(0).Value

Interesting! Have you ever tested this?

Correct, but using a Pass-Through-Query only makes sense if you insert the new record in the very same batch as otherwise you would not be in the same scope.

Yes, that's what I meant by 'for the whole operation' when I said:

you would have to use a pass-through (for the whole operation) to use it.

 

[KitaYama]

In Form_BeforeUpdate, an automatically generated primary key should already be available and readable. At this point, you also have access to the correct record and its key. The measure in Form_AfterUpdate is too late and not recommended.

I'm not sure if this is correct for sql server BE.
To the best of my knowlege, the PK will be generated when saving the record in the table.
That's one of the differences between Access and sql server BEs.

Interesting! Have you ever tested this?

Yes, that's the way I'm using it at present. But It's not correct. That's the purpose of this thread. I had a feeling the wrong returned PKs maybe because someone is inserting a record at the same time. In some cases, it's always the same number.

 

[ebs17]

I'm not sure if this is correct for sql server BE.

If this statement is not correct, then it is. CurrentDb has nothing to do with SQL Server.

Since it is about inserting a single record (?), do not use a bound form here and use an unbound form instead, use an ADODB connection (or a DAO reference) to the backend and carry out the append query and the determination of @@IDENTITY via this connection. It is important that both steps are carried out by one hand.

Why do you need the generated primary key? Actually, mostly just to be able to create records in a secondary table with this key as a foreign key.

An automatically generated surrogate key in a record hopefully serves as a replacement for a combination of fields in the record, the contents of which also correspond to a unique index. Since you know what you have passed to the record, you have another way of subsequently determining the generated primary key. This is particularly advantageous if you want to create entire series of such records with additional secondary table records, because then you only really need an append query to the main table and then an append query to the slave table.

I recommend thinking in terms of processes and procedures first and only then dealing with codes and syntax problems.
Maybe you could describe the entire process.

 

[Minty]

If this statement is not correct, then it is.

I'm sorry this makes no sense.

@kityama is saying that the AutoNumber PK is not available until the record is saved in a SQL Linked table on a bound form, which is why he needs to get it. With nearly 1800 posts, I suspect he is experienced enough to understand why he needs it.

 

[CJ_London]

I have no experience with using identity with sql server back end but in access you would not use currentdb and you would also specify the table

Something like

Dim db as dao.database
Dim pk as long
Set db=currentdb
Db.execute “INSERT INTO tblA .. “
pk=db.openrecordset(SELECT @@identity FROM tblA)(0)

 

[Pat Hartman]

Until I come to a solid solution, I changed @@Identity, to a DMax where the filter is RegisteredBy=UserFK.

Do you have triggers that generate subsequent inserts? If you don't then @@Identity is fine. If the reason you want the ID is because you need to use it as a FK then NO OTHER method actually works in a multi-user environment because using Max for example, might get the newest ID but it could easily have been added in a different context and not be the ID you need.