DAO to .mdb, ADO to .mdf comparison

This code editing a recordset based on joined tables works in DAO/.mdb database

     RS.Edit 
            RS.fields("fieldA").value = 0  'in table A
            RS.fields("fieldB").value = 0  ' in table B                
     RS.Update

The code was converted to ado on a sql server database and it failed with an error message:

Run-time error '-2147467259' (80004005)' : Cannot insert or update columns from multiple tabl开发者_如何学JAVAes.

However it appears to work if it is altered like so :

            RS.fields("fieldA").value = 0  'in table A
     RS.Update
            RS.fields("fieldB").value = 0  ' in table B                
     RS.Update

Is this a normal way to do things with sql server or is there a gotcha to it. I ask because when trying to find a solution (before I put in the extra update statement) I changed the recordset type to batchoptimistic and I got no error messge but only one table's record was edited.

Apparently, the data source of your recordset is an SQL returning data from multiple tables. Yes, it's normal that you can only update one table at a time. If you want to update values from multiple tables in a single, atomic step (so that no other client use can read the "intermediate value", where one table is changed but the other is not), you need to use a transaction.