How to get Identity value of Last Inserted Row

[jack123]

Hi All,

I am having a table with one of the Column as SerialNo whose datatype is Autonumber...

whenever I Insert a row I want to get the SerialNo of that row..

In Sql Server , there is @@Identity so if we want last inserted row Serialno we write like this...

Select @@Identity

I want to ask What is the Equivalent of @@Identity in MS ACCESS 2007.

Please If any one knows the solution ..help me out

Thanks in Advance..

 

[DCrake]

This is a snippet from a VB app that uses Access as a back end that uses the code you are looking for

Function CreateSession(WhoAmi As String)
Dim iSql As String
Dim sSql As String

'Saves the current login record back to the server
Dim NextSessionId As Integer
Dim Tbl As New ADODB.Recordset

        Set Tbl = New ADODB.Recordset
            sSql = "Select * From TblLoginSessions Where fldOrganisation = '" & szOrgCode & "'"
            
        With Tbl
            .Open sSql, MasterDbConn, adOpenKeyset, adLockOptimistic, adCmdText
            .AddNew
            .Fields("fldOrganisation").Value = szOrgCode
            .Fields("fldUserName").Value = WhoAmi
            .Fields("fldComputerName").Value = StrComputerName
            .Fields("fldLoginEvent").Value = Now()
            .Update
            .Close
        End With
        Set Tbl = MasterDbConn.Execute("Select @@identity As XPropertyID")
        LngLoginKey = Tbl.Fields("XPropertyID").Value
        Set Tbl = Nothing

End Function

 

[Acke]

Tks David for prompt response.

I managed to try simpler solution, simpler for me at least...

http://www.access-programmers.co.uk/forums/showthread.php?t=156665

 

[Mr. B]

Take a look at this Microsoft article for returning the @@identity of an auto-incrementing record ID for the last inserted row:
http://support.microsoft.com/kb/815629