Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SELECT @@IDENTITY with MS-Access

Data Manipulation

SELECT @@IDENTITY with MS-Access

by  foxbox  Posted    (Edited  )
The Jet 4.0 provider supports @@Identity:
http://support.microsoft.com/kb/232144/nl

The key to @@Identity is that it returns the value of an autoincrement column that is generated on the same connection.

This last bit is important, because it means that the Connection object used for the Insert query must be re-used without closing it and opening it up again. Access doesn't support batch statements, so each must be run separately.



So with that new information, here is the technique for obtaining this value using Access:
Code: 
<% 
    fakeValue = 5 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<conn string>" 
    sql = "INSERT someTable(IntColumn) values(" & fakeValue & ")" & _ 
        VBCrLf & " SELECT @@IDENTITY" 
    set rs = conn.execute(sql) 
    response.write "New ID was " & rs(0) 
    rs.close
    set rs = nothing 
    conn.close
    set conn = nothing 
%>

If you are unable to use JET 4.0, you can do a more risky hack like this:
Code: 
<% 
    fakeValue = 5 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<conn string>" 
    conn.execute "INSERT someTable(IntColumn) values(" & fakeValue & ")" 
    set rs = conn.execute("select MAX(ID) from someTable") 
    response.write "New ID was " & rs(0) 
    rs.close
    set rs = nothing 
    conn.close
    set conn = nothing 
%>

This is more risky because it is remotely possible for two people to "cross" inserts, and receive the wrong autonumber value back. To be frank, if there is a possibility of two or more people simultaneously adding records, you should already be considering a "real" DBMS. However, if you're stuck with Access and need more security that this won't happen, you can use a Recordset object with an adOpenKeyset cursor (this is one of those rare scenarios where a Recordset object actually makes more sense than a direct T-SQL statement):

Code: 
<% 
    fakeValue = 5 
    set conn = CreateObject("ADODB.Connection") 
    conn.open "<conn string>" 
    set rs = CreateObject("ADODB.Recordset") 
    rs.open "SELECT [intColumn] from someTable where 1=0", conn, 1, 3 
    rs.AddNew 
    rs("intColumn") = fakeValue 
    rs.update 
    response.write "New ID was " & rs("id") 
    rs.close
    set rs = nothing 
    conn.close
    set conn = nothing 
%>
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top