In my work with Access Database I needed to duplicate an existing row in the table and get an ID of newly inserted row. Below are short snippets of code showing how.
Duplicating a DataRow
// load the data into Data Table
DataTable dataTable = tableAdapter.GetData();
// clone a Data Table
DataTable duplicatedDataTable = dataTable.Clone();
// get row to duplicate
DataRow row = dataTable.Rows[0];
// import row into cloned Data Table
duplicatedDataTable.ImportRow(row);
This way we get a deep copy of the existing row and later use table adapter to insert it into database.
Getting auto-incremented ID number after inserting a row into Access Database
The actual idea of getting an auto-incremented ID of the new row in Access Database is very simple. Right after insert of the row we need to execute similar query to this one:
SELECT MAX(ID) FROM TableName
In order to avoid loading wrong ID in multi-user environment it is good to add some WHERE selection criteria which will limit selection only to duplicated rows. MAX function will pick the ID of the last inserted row.
OR 