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..

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

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

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