Q:

Select @@IDENTITY WHERE in Access

Trying to get the last ticket ID submitted by the active user in an Access database. This doesn't work unless I leave in "SELECT @@IDENTITY" by itself without the WHERE clause. How can I get it to work with the WHERE clause?
        'Pulls the last submitted ticket ID, and makes sure it was submitted by the active user to prevent a race condition scenario among multiple users
    Dim returnvalue As Integer = -1
    Dim custCMD As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT @@IDENTITY WHERE [Last Modified By] = '" & Environment.UserName & "';", connection)

    returnvalue = CType(custCMD.ExecuteScalar(), Integer)

    MsgBox(returnvalue)

A:

You don't need a WHERE clause in the SELECT @@IDENTITY because that command returns the last generated autonumber for the connection associated with your command. There is no race condition with other connections running on other pc or on other instance of your own application. Not sure about multiple threads but if you follow best practice to open/use/close the connection then you are on the safe side

