I'm just wondering what is the best way to copy a master/detail record.
Like an Order/Order Items record.
Only way I can think to do it at the moment is
1. Copy the Master data to a new record. this will generate a new id field (Auto generated primary key)
2. Query the db for max value of id in the Master.
3. Copy the Details data 1 record at a time with the foreign key set to the Master id (The details record also have Auto generated primary key).
There is always the danger that another record gets inserted into the Master between copying the data and querying for the new auto generated id.
This is unlikely as I could open the db in exclusive mode. The database file is an Access .accdb
Just wondering if .net has a better way?
