Retrieve Access AutoNumber Value After Insert

[jmcilhinney]

C# version here.

People are often asking how to retrieve the value from an AutoNumber column in an Access database after a record has been inserted. This is particularly important when working with related data, where you need to get the ID of a parent record to add to a child record. While doing this with an ADO.NET provider that accepts multiple SQL statements per batch, e.g. SqlClient, is simple, I've previously said that this cannot be done with Access and the Jet OLE DB provider without simply requerying the database. I've recently discovered that this is not the case, so I decided to create a demo project to illustrate how it can be done.

The key is the RowUpdated event of the OleDbDataAdapter. It is raised each time the adapter sends a row to the database to be saved, whether that be for an insert, update or delete. We can handle this event and detect an insert, then query the database to get the last generated ID. Because the the event is raised immediately after a row is saved, we can get the ID for multiple inserted rows and each will be correct at the time.

The attached solution was created in VS 2010, so it will require that or VB 2010 Express to open. If you don't already have either and don't want to install either, you can still at least look at the code in an older version. The required MDB file is included.

Pay most attention to the way the PK columns of each DataTable are configured to generate local IDs and how the RowUpdated events are used to retrieve the database IDs to replace them.

EDIT: I have added a second project that does basically the same thing but with a typed DataSet. I had to configure the relation in the DataSet designer to enforce a foreign key constraint and cascade updates. I also added the GetLastAutoNumber query to each table adapter. I originally tried using an extra table adapter with a single GetLastAutoNumber query so as not to duplicate. That didn't work though, because the insert was done inside a transaction and the query then could not see the new ID. By using the same table adapter for the insert and the query, the same connection and the same transaction is used.