SQL-Server @@Identity problem

[Tiger955]

Hi!

Meanwhile I read a lot about retrieving the LAST generated Identity from an SQL-server table. Everythings seems to work, but I get a wrong result.

It seems that SELECT SCOPE_Identity does not work with access when working with such a code for example:

strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; SELECT SCOPE_IDENTITY() AS TestID; "
Call SQL_PassThrough(strsql,"myTest_PT")

The function SQL_PassThrough as such works fine, 2nd parameter is an existing PT-Query with the connection and return values=yes. If no 2nd para then there is no resultset, only the insert.

So I changed it to:
strsql = "INSERT INTO tbltest ( myDate ) " _
& "SELECT '" & dteDate & "' AS dte; "
Call SQL_PassThrough(strsql)
Set rs = CurrentDb.OpenRecordset("select @@Identity as TestID from tblTest")
lastID = rs("TestID")

This brings me to my big surprise an Identity from ANOTHER (!!) table, but not the last one from tbltest.

The code is running for test reasons in another modul and not in the one I creating the received ID.

As I have to get for sure the last ID from tblTest I cannot work with DLookup, as in my multisuer App this is not sure enough.

So please help me to build a construction where I get the last ID from the table where I just made my insert.

Thanks
Michael

 

[Tiger955]

Hi!

Thanks for the links.

I went them through, all om them on this forum, but I might have to mention that I am using DAO and I RETRIEVE a result, BUT FROM another table!! That's the strange thing.

The total code is like this:

I insert a record in tblInventoryMovements and want to retrieve the MovementID.

With this MovementID I insert the detail records in tblInventoryDetails, as FK I use MovementID.

Then I am doing this in the SAME code again, I insert a new MovementID, want to retrieve the new MovementID, and insert the details again in tblInventoryDetails, but multiplying the Number of items with -1 and changing the StorageID.

As the second MovementID I get the last inserted ID from tblInventoryDetails, not the one I suggested to get, when running a Pass-Through ("Insert....; select @@Identity as MovementID from tblInventoryMovements")

The example with tblTest is just instead of the longer codes.

WHY DO I GET with the code above the last ID from tblInventoryDetails??

All links work with just one PT in the code, I run 4 PT in one code, and the PT.SQL is changed during the code. The SQL seems correct, exept that it does not return the correct ID.

Michael