0

I want to know the ID of the most recently added record.

From a posting on StackOverflow, I use

nNewID = CurrentDb.OpenRecordset("SELECT @@IDENTITY")(0)

There doesn't seem to be any reference to the table that the record has been added to - so does @@IDENTITY refer to the last added record, whichever table it was added to? Also what is the role of the (0) at the end of the statement?

braX
  • 11,506
  • 5
  • 20
  • 33
JoCaBa
  • 13
  • 2
  • Does this answer your question? [What does OpenRecordset return](https://stackoverflow.com/questions/59529165/what-does-openrecordset-return) – Rene Dec 31 '19 at 00:33
  • 1
    See https://stackoverflow.com/a/5942862/9439330. Scope is workspace, not database. @Lee Mac will update his answer (or I do). – ComputerVersteher Dec 31 '19 at 07:55

1 Answers1

2

Firstly, you should be aware that this method of obtaining the autonumber value for the most recently added record only works reliably when evaluated within the scope of the same database workspace which created the record (when using DAO) or the same database connection (when using ADO). Evaluated independently of such connection, the statement will merely yield 0.

Example

Say we have a table called Table1 with two fields:

Table1
+------------+--------+
|     ID     | Field1 |
+------------+--------+
| AutoNumber | Text   |
+------------+--------+

We can create a new record using the following code:

With CurrentDb
    .Execute "insert into table1 (field1) values ('abc')"
End With

If we want to find the value assigned to the AutoNumber field ID, we can then do this:

With CurrentDb
    .Execute "insert into table1 (field1) values ('abc')"
    Debug.Print .OpenRecordset("select @@identity")(0)
End With

What is @@IDENTITY?

Note that the SQL statement used in the above example is independent of any one particular table or field (there is no from clause), as the @@identity column merely refers to the last autonumber value issued (to any record within any table) for the current database workspace (DAO)/connection (ADO).

This T-SQL documentation may help with your understanding, but note that this applies to SQL Server, not MS Access, and so whilst this provides a description of the purpose of @@identity, not everything will translate directly to MS Access.

How Does .OpenRecordset("...")(0) Work?

As for the use of (0) directly following the .OpenRecordset method, this simply shorthand for accessing the first item in the Fields collection (which is the default member of the Recordset class), and is therefore equivalent to:

.OpenRecordset("select @@identity").Fields(0)
Lee Mac
  • 15,615
  • 6
  • 32
  • 80