We help IT Professionals succeed at work.

Access/SQL Server Getting last new record created using @@IDENTITY

Hi.  I have used @@IDENTITY before without any problems but, for some reason, I cannot get it to work in a database I am working on.  I have pasted my code below.  The message always says that the value is 0 but the new record is in fact created.

Maybe I am missing something obvious?  The code below is in a module.

We are using Access 2010 with a SQL Server 2000 backend.

Thanks,
Alexis
Public Sub testgetLastID()
Call GetLastID
End Sub

Public Sub GetLastID()
Dim db As Database, rs As DAO.Recordset
DoCmd.RunSQL "INSERT INTO dbo_vwOrders(CustomerID) VALUES(6)"
Set rs = CurrentDb.OpenRecordset("SELECT @@IDENTITY As NewID FROM dbo_vwOrders", dbReadOnly)
newid = rs("NewID")
MsgBox "newid " & newid
Set rs = Nothing
End Sub

Open in new window

Comment
Watch Question

Top Expert 2016
Commented:
try using ADO recordset

Dim rst As New ADODB.Recordset

CurrentProject.Connection.Execute "INSERT INTO dbo_vwOrders(CustomerID) VALUES(6)"


rst.Open "SELECT @@IDENTITY As NewID FROM dbo_vwOrders", CurrentProject.Connection
MsgBox rst("NewID")

rst.Close
Set rst = Nothing

Author

Commented:
Thanks, capricorn1.  I thought I had tried ADO from a post I found on EE but I must done it wrong beause, when I tried your sample code, it worked.

I really appreciate your help.

Alexis
Scott McDaniel (EE MVE )Infotrakker Software
Most Valuable Expert 2012
Top Expert 2014

Commented:
You can use DAO, but you must be very careful to "scope" the connection and workspace correctly. IMO you're better off using ADO, as suggested by cap - or use a Stored Procedure, assuming you're working with SQL Server, as the "dbo_" qualifier woud suggest.

Here's a post about using DAO with @@IDENTITY: http://stackoverflow.com/questions/5942781/select-identity-not-scoped-by-db-object

That said, the correct syntax for @@IDENTITY is this:

SELECT @@IDENTITY As NewID

You don't specify the table, and in fact @@IDENTITY will provide you with the last inserted value, regardless of where that value might be. In most cases it will be the table you expect, of course, but just be aware that when dealing with a database that supports triggers it is entirely possible that the ID you retrieve is NOT the one you expect. For example, if the dbo_vwOrders includes a table that fires a trigger to insert a record (perhaps into an Audit or Security table), you may very well end up with the identity for THAT table, and not the one you expect.

There are other functions that can return explicit values, like IDENT_CURRENT or SCOPE_IDENTITY, but those are not available on the Access platform. However, if you're dealing with SQL Server, you are far better off using a Stored Procedure to do this, if you must return the correct ID value. If you use SPs then you can use those other functions, and you can be sure that you're returning the correct values.

Author

Commented:
Thanks, LSMConsulting.  I wish I could award you some points for your awesome explanation but I already closed the question.  Yes, we are using SQL Server but I have not used any triggers.  I also use Stored Procedures.  I will certainly keep your info in mind if I ever need to use triggers.

Alexis