CODEWITHSUNDEEP
sqlvb.netms-access
KoolKabin
KoolKabin

Reputation: 17703

How do we get last inserted record in msaccess query

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?

Upvotes: 1

Views: 20766

Answers (5)

HackSlash
HackSlash

Reputation: 5811

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

Upvotes: 0

Fionnuala
Fionnuala

Reputation: 91376

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.

Upvotes: 5

Alex Essilfie
Alex Essilfie

Reputation: 12613

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).

Upvotes: -1

competent_tech
competent_tech

Reputation: 44971

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.

Upvotes: -1

ivan
ivan

Reputation: 430

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.

Upvotes: -1

Related Questions