Transaction basic question DAO

[J@b@r]

Hello,

I am using a GUI,made in VB6, that is the interface to change records in a database.

To gain access to the database I use DAO. (I am aware of the existence of ADO).

I have to update 4 tables that I have opend in a recordset.
I have to do this table by table and if anything goes wrong I need to be able to rollback to the initial state of the recordsets.

--> Sollution Transaction.

For the question:

A)
Shoul I Begin the transaction after opening the workspace , dtabase and before opening the four Recordsets("Tables").

OR:
B)Should I open the workspace, database and recordsets and then start the transaction ?

C)None of the above. In this case, in what way should it then be done.

thx for the info.
Greetz
Jabar

[mgiambrone]

Choose option B but then write valadation routines before trying any Update attempts.

Your validation code should validate the data.

If everything is OK, go ahead and Update the tables.

Are you using MS Access or SQL Server??

[J@b@r]

I am using a Access 7.0 database made with the visdata manager of vb 6. (equals Access 97 .mdb type database).
It is a local application used by 1 person on 1 pc.

The validation is done when the user clicks the ok (Save) button.
When the validation is done, the changes are implemented in the database.

Thank you for taking the time to answer my question.
Greetz,
J@b@r.

[techgnome]

there's a problem w/ using transactions that needs to be addressed. If you open a table within a transaction, it becomes locked until the transaction is committed or rolledback.
Just something to think about.

[J@b@r]

Is there another sollution to make sure that changes in multiple tables are applied fully ?

J@b@r

[techgnome]

Here's how I would do it.....
First, I'd use ADO, just because I know it had Transactions enabled in it, but that's another issue....

1) Open the DB, get the data, disconnect it, and close everything
2) Let the user makes changes
3) Validate
4) reconnect to the db, begin a transaction, update the data to the db, if all was OK, commit it, if not, roll it back
5) Close the conenction & DB
6) Report Success/Failure