Full Version: @@Identity in Multi-User setting
UtterAccess Forums > Microsoft® Access > Access Forms
I have the following code attached to a submit button on a form. When clicked it updates the entered information to tblContacts. It then returns the primary key, lngContactID, to the text field txtCID. I am having trouble finding out if this will work in a multi-user environment.
Any suggestions or alternatives??
Private Sub cmdSubmit_Click()
On Error GoTo Err_cmdSubmit_Click
Dim strSQL As String
Dim rs As ADODB.Recordset
Dim LastAddedContactID As Variant
strSQL = "INSERT INTO tblContacts (chrFirstName, chrLastname, chrAddress1, chrAddress2, chrCity, chrState, chrZipCode, chrPhoneNumber) "
strSQL = strSQL & "VALUES('"
strSQL = strSQL & Me.txtFirstName.Value & "',"
strSQL = strSQL & "'" & Me.txtLastName.Value & "',"
strSQL = strSQL & "'" & Me.txtAddress1.Value & "',"
strSQL = strSQL & "'" & Me.txtAddress2.Value & "',"
strSQL = strSQL & "'" & Me.txtCity.Value & "',"
strSQL = strSQL & "'" & Me.txtState.Value & "',"
strSQL = strSQL & "'" & Me.txtZipCode.Value & "',"
strSQL = strSQL & "'" & Me.txtPhoneNumber.Value & "')"
CurrentProject.Connection.Execute strSQL
Set rs = CurrentProject.Connection.Execute("select @@identity")
LastAddedContactID = rs.Fields(0).Value
Me.txtCID.Value = LastAddedContactID
Set rs = Nothing
Me.cmdSubmit.Visible = False
Me.cmdClear.Visible = False
Me.cmdEdit.Visible = True
    Exit Sub
    MsgBox Err.Description
    Resume Exit_cmdSubmit_Click
End Sub

Thanks. This site is awesome and always proves a trusty resource.
No, this isn't going to work consistently - it's possible another record gets added before the identity is returned.
TW, There is no @@identity value in Access -isn't that a SQL-Server-ism?
Others may have another way but I usually use a Recordset to do this and save the primary key:
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset("tblHousehold")
        With rst
            !Zip = Trim(sZip)
            !Address = Trim(sAddress)
            getHouseholdID = !HouseholdID   ' save the primary key
        End With

This snippet (using DAO instead of ADO) opens the tblHousehold table, adds a new record by setting some field values and then gets the AutoNumber ID into the "getHouseHoldID" variable and saves the record (.Update).
Perhaps someone can post an ADO example?
@@Identity is also ADO and works with VB.
See this KB Article.
Actually it should have been this article.
So, if I understand this correctly, I'll use that instead of my SQL statement?
Can you briefly explain that 'trim' function??
For me, as an example I would use this code:
set dbs = currentDb()
set rst = dbs.OpenRecordset("tblContacts")
With rst
        !chrFirstName = Trim(txtFirstName)
        !chrLastName = Trim(txtLastName)
        getLastAddedContactID = !lngContactID
End With

Ocan then set the value of txtCID to LastAddedContactID with??:
txtCID.Value = LastAddedContactID

Thanks for the help.
So, the code I have will work in a multi-user setting?
I'm concerned that if two users add new records simulataneously the wrong ID will be returned?
As long as they are not using the same connection, the correct ID will be returned. At least that is what the KB Article says.
Well, each user accesses the database through a shortcut on his/her PC....the database is split, and the shortcut opens the front end of the database.
imagine this would work??
Thanks so much.
Thanks! I didn't know this - nice feature of ADO.
The Trim() function removes any spaces on both ends of a string. The LTrim() removes spaces on the Left end of a string and the RTrim() removes spaces on the Right end of the string.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.