Get last ID of primary key automatically generated

[California2013]

I insert a new record in a table where primary key is automatically generated. I need to get this number (primary key) immediately and send it to client code (MS Dymanics NAV, which uses .NET methods).

I do know how to manage this issue using MS SQL Server and .NET code (using a stored procedure with an output parameter and @@lastid system parameter to get last ID).



Do you know how to manage it using MS Access? Maybe using a query object as a stored procedure?

[RuralGuy]

Here's a couple of methods:

From MVP Ken Snell
rst.AddNew
    rst.Fields("FieldName1").Value = "YourNewValue1"
    rst.Fields("FieldName2").Value = "YourNewValue2"
'  (etc. -- do not include the autonumber field as one that
'   is given a value)
rst.Update
rst.Bookmark = rst.LastModified
lngAuto = rst.Fields("AutonumberFieldName").Value
 
'---  Another approach
 
From MVP Allen Browne
Function ShowIdentity() As Variant
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    Set db = DBEngine(0)(0)
    db.Execute "INSERT INTO MyTable ( MyField ) SELECT 'nuffin' AS Expr1;"
 
    Set rs = db.OpenRecordset("SELECT @@IDENTITY AS LastID;")
    ShowIdentity = rs!LastID
    rs.Close
 
    Set rs = Nothing
    Set db = Nothing
End Function