Full Version: @@Identity in Multi-User setting
UtterAccess Forums > Microsoft® Access > Access Forms
squeezedtoad
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??
CODE
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
rs.Close
Set rs = Nothing
Me.cboVDN.SetFocus
Me.cmdSubmit.Visible = False
Me.cmdClear.Visible = False
Me.cmdEdit.Visible = True
Exit_cmdSubmit_Click:
    Exit Sub
Err_cmdSubmit_Click:
    MsgBox Err.Description
    Me.txtFirstName.SetFocus
    Resume Exit_cmdSubmit_Click
End Sub

Thanks. This site is awesome and always proves a trusty resource.
Matt
mishej
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:
CODE
        Set dbs = CurrentDb()
        Set rst = dbs.OpenRecordset("tblHousehold")
        
        With rst
            .AddNew
            !Zip = Trim(sZip)
            !Address = Trim(sAddress)
            getHouseholdID = !HouseholdID   ' save the primary key
            .Update
        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?
truittb
John,
@@Identity is also ADO and works with VB.
See this KB Article.
truittb
Actually it should have been this article.
squeezedtoad
Thanks!
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:
CODE
set dbs = currentDb()
set rst = dbs.OpenRecordset("tblContacts")
With rst
        .AddNew
        !chrFirstName = Trim(txtFirstName)
        !chrLastName = Trim(txtLastName)
        getLastAddedContactID = !lngContactID
        .Update
End With

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

Thanks for the help.
MC
squeezedtoad
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?
truittb
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.
squeezedtoad
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.
mishej
Thanks! I didn't know this - nice feature of ADO.
truittb
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.