Re: Begin Tran open disconnected recordset - reconnect - rollback/comm

Is is possible to commit or rollback under the following circumstances
using
ADO?

Must be. We do it here.

1. Begin a Tran on a database
2. Open a recordset and disconnect it
3. Perform multiple operations on the disconnected recordset
4. Reconnect and updatebatch on the recordset with adMarshallAll (default)

Yes but at this stage here, a transaction is associated with a particular
ADO Connection (I am assuming that you are using ADO's transactions)
Therefore in step 2, the Recordset may be disconnected, but I assume you
have kept the Connection alive that the Transaction in step 1 is associated
with.
And if you kept that Connection alive then your "reconnect" amounts to
reintroducing your Recordset to the same live Connection.

5. Filter (ModifiedRS.Filter = adFilterConflictingRecords)
6. When ModifiedRS.recordcount>0
6a ROLLBACK initial Tran from step 1.
7. Whne ModifiedRS.recordcount=0
7a COMMIT initial Tran from step 2.

But steps 5, 6, 7 are not necessary.
If a COM Exception is thrown => do a Rollback
If a COM Exception is not thrown => do a Commit

Given above sequence, if Rollback or Commit is completed
A) Will TRANSACTION effect the updatebatch condition given that the record
was opened after the Begin Tran?

You mean the other way round.
There is a pending transaction when UpdateBatch is called => whatever
happens in UpdateBatch in terms of INSERTs/UPDATEs/DELETEs will be in the
transaction

B) Will the fact that the recordset is disconnected mean that the
commit/rollback tran will not effect the records from that batch?

Neither. It depends entirely on whether yo uare using the same Connection
object that has the transaction or not.
If it is the same object => Recordset is covered in the Transaction
If it is a different object => Recordset is not covered in the Transaction

Stephen Howe


.

Relevant Pages

  • RE: Rollback transaction
    ... Write a common routine that will create a single connection object. ... Begin and End transaction block, read each recordset and update the database. ...
    (microsoft.public.vb.database.ado)
  • Re: Begin Tran open disconnected recordset - reconnect - rollback/
    ... I thought the key was the connection. ... Perform multiple operations on the disconnected recordset ... Yes but at this stage here, a transaction is associated with a particular ... 6a ROLLBACK initial Tran from step 1. ...
    (microsoft.public.data.ado)
  • Re: Connection types and speeds
    ... The memory used by holding the connection open is not ... All recordset inserts seen to consequently have 0ms execution time, ... actually written to the database while the code has regained control ... RecordsetClone of a form) when a transaction was rolled back, ...
    (microsoft.public.access.queries)
  • Re: commit or rollback a transaction without first beginning a transaction
    ... I understood the opposite; that when a recordset ... the recordset before entering the transaction, ... > what happens with a Jet recordset. ... >> a single connection, so when SQLTransact is called, all statements on the ...
    (microsoft.public.access.modulesdaovba)
  • Re: Delete event not followed up with BeforeDelConfirm or AfterDel
    ... I take it you are trying to wrap your own transaction around all the ... inserts, edits, and deletes in both a main form and a subform. ... decision to either commit or rollback. ... recordset within that transaction, open a form, and assign your recordset to ...
    (microsoft.public.access.forms)