Inserting a new record into a database and instantly retrieving the ID (primary key) that Access produced is a common necessity when dealing with Microsoft Access. This typically occurs when a table's primary key is an AutoNumber field.

To put it simply, you want to know the unique ID that was generated for each row after it has been inserted. This article provides clear examples and straightforward, useful methods for returning the ID of a freshly inserted entry in Access SQL.

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.

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.

HostForLIFE.eu SQL Server 2022 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.