Whats the best way to update a database using data relations?

[Ouranos]

I have three tables and two data relations connecting them:

(Table 1 -> Table 2 <- Table 3)

I'm using an MS Access database and the primay key column in each table uses a auto number.
The data relations have also been set up to cascade updates and deletes.
At the moment I'm using 3 data adapters to create the tables in the data set which seems to be working

Now I need to add, delete and update records in the database but I'm strugging to find anything useful. Does anyone know of the correct way to do this?

[jmcilhinney]

You use the same data adapters to save changes. It appears that you have a many-to-many relationship their, so two parent tables and one child join table. You would use the same three data adapters to save as you used to retrieve. There are a couple of gotchas though:

1. You must save in the correct order. Just as you must retrieve the parent tables first to avoid violating constraints in your DataSet, so you must save the parent tables first to avoid violating constraints in the database. You should wrap all the Update calls in a transaction, either using an OleDbTransaction or, preferably, a TransactionScope. You should also set the AcceptChangesDuringUpdate properties of your data adapters to False and only call AcceptChanges on the whole DataSet after committing the transaction.

2. If you insert new records into the parent tables and you want to then insert corresponding records into the child tables, you need to know the parent IDs generated by the database. That can be trivial with a "proper" database like SQL Server, but not so with Access. Follow the CodeBank link in my signature and check out my thread on Retrieving AutoNumber Values.

[dadazhu]

To get the new autoNumber ID from the parent table, I use this before closing the RS.

    rs.Update
    ERID = rs.Fields("ID").Value

[jmcilhinney]

To get the new autoNumber ID from the parent table, I use this before closing the RS.

    rs.Update
    ERID = rs.Fields("ID").Value

That's all well and good if you're using a Recordset but we're not in VB6 any more Toto so we don't use ADO. If you're using VB.NET then use VB.NET, which means using ADO.NET for data access. The OP said that they were using data adapters so they obviously already are.