Retrieve a record identity from an Access database autonumber field on INSERT

ASP / Access

This article shows one method of retrieving the identity from the autonumber field of the newly inserted record in an Access database.

The code

<%
Set commInsert = Server.CreateObject("ADODB.Connection")
commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data_
    Source=C:\WebSiteDirectory\DatabaseDirectory\YourDB.mdb; Persist_
    Security Info=False" ' Replace with your OLE DB connection string.
commInsert.Execute("INSERT INTO tableName(TextField, NumericField)_
    VALUES('ABCDE', 12345);") ' Execute the insert command
Set rsNewID = commInsert.Execute("SELECT @@IDENTITY") ' Create a recordset and_
    SELECT the new Identity
intNewID = rsNewID(0) ' Store the value of the new identity in variable intNewID
rsNewID.Close
Set rsNewID = Nothing
commInsert.Close
Set commInsert = Nothing
%>

NOTE: I have used underscores to represent line continuations, click here to see the example full width.

IMPORTANT
To use this method you must use OLE DB to connect to the database. Click here for instructions on setting up an OLE DB connection to an Access database.

Notes for dreamweaver users

The code example above should be used instead of Dreamweaver's Insert Record behaviour.

You can also modify the following line:

commInsert.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data_
    Source=C:\WebSiteDirectory\DatabaseDirectory\YourDB.mdb; Persist_
    Security Info=False" ' Replace with your OLE DB connection string.

To use the connection already defined in the file within your connections directory, provided that it uses an OLE DB connection string, for example:

commInsert.Open MM_YourConnection_STRING