How Do I Return the ID of a Newly-Inserted Row in Access SQL?

Introduction

When working with Microsoft Access, a common requirement is to insert a new record into a table and immediately retrieve the ID (primary key) that Access automatically generated. This usually happens when a table uses an AutoNumber column as its primary key.

In simple words, after inserting a row, you want to know the unique ID that was created for that row. This article explains how to return the ID of a newly inserted row in Access SQL using simple and practical approaches with clear examples.

Understanding AutoNumber and Primary Key in Access

In Access, many tables use an AutoNumber field as the primary key. This means Access automatically assigns a unique number to each new record.

Example:

• Table name: Orders

• Primary key column: OrderID (AutoNumber)

When you insert a new order, Access automatically generates an OrderID. The challenge is retrieving this value after the insert.

Using @@IDENTITY to Get the Last Inserted ID

One of the simplest ways to retrieve the last inserted ID in Access SQL is to use @@IDENTITY. This returns the AutoNumber value generated by the most recent INSERT operation in the current database session.

Example:

INSERT INTO Orders (CustomerName, OrderDate)
VALUES ('Amit', Date());

SELECT @@IDENTITY AS NewOrderID;

Explanation:

• The INSERT statement adds a new row

• @@IDENTITY returns the AutoNumber value created by that insert

This approach is simple and commonly used in small applications.

Using DAO Recordset to Insert and Return ID (Recommended)

For VBA-based Access applications, using DAO (Data Access Objects) is one of the safest and most reliable methods.

Example in VBA:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("Orders", dbOpenDynaset)

rs.AddNew
rs!CustomerName = "Amit"
rs!OrderDate = Date
rs.Update

MsgBox rs!OrderID

rs.Close
Set rs = Nothing
Set db = Nothing

Explanation:

• A new record is added using AddNew

• After Update, the AutoNumber ID is immediately available

• This method avoids conflicts and is very reliable

Using ADO to Return the Inserted ID

If your application uses ADO instead of DAO, you can still retrieve the inserted ID, but it requires a slightly different approach.

Example using ADO:

Dim cn As Object
Dim rs As Object

Set cn = CurrentProject.Connection
Set rs = CreateObject("ADODB.Recordset")

rs.Open "SELECT * FROM Orders", cn, 1, 3

rs.AddNew
rs.Fields("CustomerName").Value = "Amit"
rs.Fields("OrderDate").Value = Date
rs.Update

MsgBox rs.Fields("OrderID").Value

Explanation:

• The recordset is opened in editable mode

• After adding the record, the AutoNumber field is available

Using INSERT with a Follow-Up SELECT Query

Another practical approach is to run an INSERT query and then immediately fetch the last ID.

Example:

INSERT INTO Orders (CustomerName, OrderDate)
VALUES ('Amit', Date());

SELECT MAX(OrderID) AS LastID FROM Orders;

Important note:

• This works only in single-user scenarios

• In multi-user systems, another insert may happen at the same time

Common Mistakes to Avoid

Many beginners face issues when trying to return the inserted ID. Common mistakes include:

• Using MAX(ID) in multi-user environments

• Running @@IDENTITY in a different connection

• Forgetting to use AutoNumber as primary key

Understanding these mistakes helps avoid incorrect results.

Comparison Table: DAO vs ADO vs @@IDENTITY

The table below helps you quickly decide which approach is best for your use case.

Method	Best Used When	Reliability	Multi-User Safe	Complexity
DAO Recordset	VBA-based Access applications	Very High	Yes	Medium
ADO Recordset	External connections or mixed environments	High	Yes	Medium
@@IDENTITY	Simple SQL inserts	Medium	No (connection dependent)	Low

Real-World Example: Access Form → Table Insert

A very common real-world scenario is inserting data from an Access form into a table and then using the generated ID.

Example:

• Form name: frmOrders

• Table name: Orders

• Primary key: OrderID (AutoNumber)

VBA code behind the form button:

Private Sub btnSave_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset

    Set db = CurrentDb
    Set rs = db.OpenRecordset("Orders", dbOpenDynaset)

    rs.AddNew
    rs!CustomerName = Me.txtCustomerName
    rs!OrderDate = Me.txtOrderDate
    rs.Update

    MsgBox "Order saved with ID: " & rs!OrderID

    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

Explanation:

• Data is taken from form controls

• Record is saved into the Orders table

• The AutoNumber OrderID is immediately available

This approach is widely used in real-world Access applications.

Troubleshooting Common Runtime Errors

While working with Access inserts, developers may face common issues.

Error: ID Returns Null or 0

Cause:

• Recordset not updated properly

Solution:

• Ensure rs.Update is called before reading the ID

Error: @@IDENTITY Returns Wrong Value

Cause:

• Another insert happened in the same session

Solution:

• Avoid @@IDENTITY in multi-user environments

• Prefer DAO Recordset

Error: Recordset Is Not Editable

Cause:

• Incorrect recordset type

Solution:

• Use dbOpenDynaset when opening the recordset

Error: Field Not Found

Cause:

• Incorrect field name or spelling

Solution:

• Verify column names in the table

Which Method Should You Use?

The best method depends on how your Access application is built:

• Use DAO Recordset for VBA-based Access apps

• Use @@IDENTITY for simple SQL-based inserts

• Avoid MAX(ID) for production systems

Choosing the right approach ensures accurate and reliable results.

Summary

Returning the ID of a newly-inserted row in Access SQL is a common and important task. You can use @@IDENTITY for simple cases, DAO Recordsets for reliable VBA solutions, or ADO when working with external connections. Understanding how AutoNumber fields work and choosing the correct method ensures you always retrieve the correct ID after inserting a record.