Home|Sitemap|Contact

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


Last Identity Value: 1078 


<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Option Explicit
Session.CodePage=65001
Response.Charset="UTF-8"

Dim rs, message

Sub getRecordSet
  Dim conn, command, connection
  ' MS Access sometimes balks with select @@identity
  command = "INSERT INTO identityTable (tfield) VALUES ('TEST')"
  ' For MS SQL
  ' command = "INSERT INTO identityTable (tfield) VALUES ('TEST'); SELECT @@identity"
  on error resume next

  ' Create an ActiveX Data Object for the database connection
  Set conn = Server.CreateObject("ADODB.Connection")
  if err then
    Response.Write "There was an error creating the database connection<br>" & vbLF
    '* developer only * Response.Write err.number & "<br>" & err.description & "<br>" & err.source
    Response.End
  else
    ' OLEDB connections offer better performance and stability than ODBC.
    ' Refer to these Microsoft Knowledge Base articles: KB Q10191, KB Q10023
    connection = YOUR CONNECTION STRING
    ' Try to open a connection to the database
    conn.open connection
    if err then
      Response.Write "There was an error opening the database connection<br>" & vbLF
      '* developer only * Response.Write err.number & "<br>" & err.description & "<br>" & err.source
      Response.End
    else
      ' For MS SQL comment out next two lines
      conn.execute(command)
      command="SELECT @@IDENTITY;" 
      Set rs = conn.execute(command)
      if err then
        Response.Write "There was an error opening the recordset to retrieve the requested data.<br>" & vbLF
        '* developer only * Response.Write err.number & "<br>" & err.description & "<br>" & err.source
        Response.End
      else
        ' we have our recordset or results, lets go.
        Exit Sub
      end if
      set rs = nothing
      conn.close
    end if
    Set conn = nothing
  end if
  on error goto 0
End Sub

call getRecordSet

' Was the subroutine successful? (Did we get a recordset object)
if isObject(rs) then

  ' And do we have records?
  if NOT (rs.bof AND rs.eof) then
    '*******************************************
    '* M A I N R O U T I N E B E G I N *
    '*******************************************
    message = rs(0)
  end if
  rs.close
  Set rs = nothing 
end if
%>
<!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, 2006 Roderick Divilbiss">
</head>

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

</html>