Can Transaction be used with recordsets?

[Binish]

Hi All,

Can BeginTrans and CommitTrans be used with ADO recordsets as well? I am using 2 recordsets with rs.AddNew and rs.Update. Can I use BeginTrans and CommitTrans in this case? Or is it applicable only for Conn.Execute?

All help will be apreciated.

Thanks,

Binish

[d3gerald]

recordsets dont need those lines of code, if you add a new record, you can directly say, rs.Addnew then supply the values to the fields. this can be done provided your recordset is directly connected to a single table in your database only and not a result of a stored procedure

[ganeshmoorthy]

I hope BeginTrans and CommitTrans can be used only with the Connections and not with Recordsets...just before your addnew put BeginTrans and after the update put CommitTrans...

Conn.BeginTrans
rs.AddNew
'.....
rs.Update
Conn.CommitTrans

[Binish]

In my case, I need to make sure either both my recordsets are updated after AddNew or None. Typically a case where I need to use a transaction

[ganeshmoorthy]

Conn.BeginTrans
rs.AddNew 'Your 1st Recordset
'.....
rs.Update
 
rs1.AddNew 'Your 2nd Recordset
'....
rs1.Update
Conn.CommitTrans

This will make sure both the recordsets are updated, else none...

[Binish]

I hope BeginTrans and CommitTrans can be used only with the Connections and not with Recordsets...just before your addnew put BeginTrans and after the update put CommitTrans...

Conn.BeginTrans
rs.AddNew
'.....
rs.Update
Conn.CommitTrans

I will definitely try this. Hope that will solve the problem

[ganeshmoorthy]

have you checked my other post too...

[Binish]

Conn.BeginTrans
rs.AddNew 'Your 1st Recordset
'.....
rs.Update
 
rs1.AddNew 'Your 2nd Recordset
'....
rs1.Update
Conn.CommitTrans

This will make sure both the recordsets are updated, else none...

I will try this and come back. Thanks a lot!

[mikee_phil]

you should put an error handler incase an error occur on any of the recordset
error_handler:
debug.print rs.status
debug.print rs1.status
conn.rollbacktrans
err.clear

adRecOK 0 The record was successfully updated.
adRecNew 1 The record is new.
adRecModified 2 The record was modified.
adRecDeleted 4 The record was deleted.
adRecUnmodified 8 The record wasn't modified.
adRecInvalid &H10 The record wasn't saved because its bookmark is invalid.
adRecMultipleChanges &H40 The record wasn't saved because it would affect multiple records.
adRecPendingChanges &H80 The record wasn't changed because it refers to a pending insert.
adRecCanceled &H100 The record wasn't saved because the operation was canceled.
adRecCantRelease &H400 The record wasn't saved because of existing record locks.
adRecConcurrencyViolation &H800 The record wasn't saved because optimistic concurrency was in use.
adRecIntegrityViolation &H1000 The record wasn't saved because it would violate integrity constraints.
adRecMaxChangesExceeded &H2000 The record wasn't saved because there were too many pending changes.
adRecObjectOpen &H4000 The record wasn't saved because of a conflict with an open storage object.
adRecOutOfMemory &H8000 The record wasn't saved because of an out-of-memory error.
adRecPermissionDenied &H10000 The record wasn't saved because the user had insufficient permissions.
adRecSchemaViolation &H20000 The record wasn't saved because it doesn't match the structure of the database.
adRecDBDeleted &H40000 The record had already been deleted from the database.

[Binish]

That was really cool. Thanks a lot!