How do we get last inserted record in msaccess query

Question

I am inserting data from my vb.net application to msaccess db.

I am confused in way of getting the last inserted record added to a table. IN MS-SQL we get @@IDENTITY for that but it didn't worked for me in MSAccess.

so what should be do for getting the last inserted record added to a table?

What do you mean didn't work? If you create an object and execute the query against that, you can use @@identity. http://stackoverflow.com/questions/2082427/how-to-retrieve-last-autoincremented-value-in-ms-access-like-identity-in-sql-s, http://stackoverflow.com/questions/5942781/select-identity-not-scoped-by-db-object and so on. — Fionnuala, Dec 16 '11 at 15:56

score 5 · Accepted Answer · answered Dec 16 '11 at 16:09

5

Example:

Dim db As Database
Set db = CurrentDb

db.Execute "INSERT INTO Table1 (atext) Values('abc')", dbFailOnError

Dim rs As dao.Recordset

Set rs = db.OpenRecordset("select @@identity")
Debug.Print rs(0)

It does require that there is an autoincrement key on the table.

answered Dec 16 '11 at 16:09

Fionnuala

90,370
7
114
152

The question is about VB.NET and this answer is written in VBA... – HackSlash Jun 22 '22 at 17:06

score 0 · Answer 2 · answered Jun 22 '22 at 17:14

Here is a short example using OleDb. Notice that I create a command from the connection and then reuse that object to make my select identity call. This ensures we are in the same scope and get the identity of the record we just inserted. This has the same effect of chaining the commands together with ";", like you would want to do in other DB SQL calls to return the identity with the insert command. ExecuteScalarAsync returns the response object which we can cast to our ID type.

Dim Identity As Integer
Dim recordsAffected As Integer

Using connection As New OleDbConnection(ConnectionString)
    Await connection.OpenAsync()

    Using command = connection.CreateCommand()
        command.CommandText = "INSERT INTO table (field) VALUES (?)"
        recordsAffected = Await command.ExecuteNonQueryAsync()

        ' Get the ID of last inserted record
        command.CommandText = "SELECT @@IDENTITY"
        Identity = CInt(Await command.ExecuteScalarAsync())
    End Using

    connection.Close()
End Using

score -1 · Answer 3 · answered Dec 16 '11 at 11:20

-1

It's always a good practice to have a numeric (even auto-increment) primary key. Then you can always select the MAX and that's the latest inserted record.

answered Dec 16 '11 at 11:20

ivan

390
3
13

hmm sounds interesting... but what if two users inserts records simultaneously before running the select statement... – KoolKabin Dec 16 '11 at 14:35
You cannot depend on an auto-increment primary key to be anything but unique, if order is important, you need to build your own, and then you are into all sorts of interesting work for multiple users. – Fionnuala Dec 16 '11 at 15:58
@KoolKabin: Store the user name who created the record, so then you can get the MAX where "created by" equals the current user. Not ideal, but better. The ideal solution would be to move to SQL Server Express (it's free). – HardCode Dec 16 '11 at 16:59
@HardCode Identity works fine with Access, as long as you have an autonumber. SQL Server Express is not a substitute for Access, only for the back-end database used with Access. Also, maintenance is much more work. The right tool for the job is not always industrial strength. – Fionnuala Dec 16 '11 at 20:34
The OP is using VB.NET as a front-end, not Access. To me, that justifies the app to be significant enough to ditch it for SQL Express or even another DB engine instead. I've worked extensively with both Access and SQL Server, and the maintenance to me is much more on Access with the problems it has, over maintenance of SQL Server (Express) to administer it. – HardCode Dec 16 '11 at 22:04

score -1 · Answer 4 · answered Dec 16 '11 at 15:50

-1

It's more complicated in Access than SQL Server because access doesn't support the execution of multiple statements in a batch or output parameters.

According to the MSDN documentation, you need to add a handler for the RowUpdated event.

Before resorting to this, however, I would try wrapping your insert code in a transaction and then executing the select @@identity method within the transaction. Might not work, but worth a shot.

answered Dec 16 '11 at 15:50

competent_tech

44,465
11
90
113

You don't need to use a transaction. As long as you use the same connection object, you get the last inserted ID by using @@identity – Albert D. Kallal Aug 19 '14 at 23:57

score -1 · Answer 5 · answered Dec 16 '11 at 16:02

-1

As far as I know, MS Access does not have the functionality to get the last added row.

In practice, I create an autoincrement column (which is usually the Primary Key anyway). Then I run this query when I desire to get the last row in the table:

SELECT TOP 1 * FROM [Table] ORDER BY [IdColumn] DESC

It simply sorts the the rows in the table by the ID column in reverse order and takes the first one (which is really the last row in the table).

answered Dec 16 '11 at 16:02

Alex Essilfie

12,339
9
70
108

Access supports use @@identity. So the posted solution of using select @@identity works just fine. So yes, this feature is built in and your answer is wrong. – Albert D. Kallal Aug 19 '14 at 23:58

How do we get last inserted record in msaccess query

5 Answers5

Linked