Get The Identity Value Of The Last Inserted Record In MS-Access Or MS SQL

Execute the SQL cmdTxt: SELECT @@IDENTITY; after entering the record. For MS-SQL you can append that cmdTxt to the SQL cmdTxt you are using to insert the record. For MS-Access you will probably need to execute the cmdTxt immediately after you execute the insert statement. This leaves a possibility that another insert may occur before your SELECT @@IDENTITY; cmdTxt and you might get the wrong value; (although this is a small risk.)

Note the use of the paramSQL.asp library which encapsulates the data access and performs extensive error handling. It also greatly reduces the number of lines of code we need to write to access the database.

<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Option Explicit
Session.CodePage=65001
Response.Charset="UTF-8"
%>
<!--#include virtual="include/paramSQL.asp"-->
<%
Dim cmdTxt, lastID
cmdTxt = "INSERT INTO identityTable (tfield) VALUES ('TEST')"
openCommand Application("examples"),"getIdentity 1"
execCmd cmdTxt

cmdTxt="SELECT @@IDENTITY;"
getRS db_rs, cmdTxt, "getIdentity 2"

if NOT (db_rs.bof AND db_rs.eof) then
    lastID = db_rs(0)
end if
closeCommand
closeRS
%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>

<head>
<title>MS Access @@Identity</title>
<meta name="author" content="Roderick Divilbiss">
<meta name="copyright" content="© 2005-2010 Roderick Divilbiss">
</head>

<body>
Last Identity Value: <%=lastID%>
</body>

</html>

Except where otherwise noted, this site, all content, and all source code and markup is licensed under a Creative Commons License Creative Commons License.

No part of this web site including all application code and examples may be used for commercial purposes without prior written permission from the author, Roderick W. Divilbiss of Overland Park, Kansas, United States of America.

How can you get the auto-number (identity) value from the last inserted record in MS Access?