Unanswered: Static Recordset best practices question
I'm developing on a VB 6.0/MSSQL app with an ADO-based data layer. To access the db, we're using static recordsets exclusively to minimize server load. My question is regarding methods for updating data on both the server and client sides in a manner that minimizes server load and safely keeps the client's recordset (data) in sync with the db.
Let's say a client edits a field on a form that has been filled from a static recordset and then hits the [UPDATE] button. I see two ways to update both the server's database and the client's recordset:
1. Run the stored procedure that updates the db, then rerun the stored procedure that fills (selects) the recordset. This ensures that the client's data matches the server's data exactly, but wastes a lot of bandwidth on just one row of a large recordset.
2. Run the stored procedure that updates the db, but alter the data in the recordset locally. This reduces server load, but runs the risk of introducing discrepancies (e.g. formatting differences) between the server data and the client data.
Is there a right way and a wrong way? Conventional wisdom? Certain pitfalls to watch out for?