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

Just to continue on this line of thought. There are actually two
co-dependent connections in the legacy application [which used DAO]. It tied
the two together using a Workspaces object that was ODBC bound to an ACCESS
database through table linking with many tables from the SQL Server 7.0
databases. The queries ran through this ODBC connection channel and the
rollback was only through the single ODBC channel.

While I can still follow the rule to open the connection and rollback
depending on the presence of the COM exception - I suspect that for this
case, what must happed is this - Establish a connection for each recordset,
maintain both, begin tran on both, and commit or rollback depending on
whether one or each of them has a COM exception.

Is this correct?

"Jamie" wrote:


I thought the key was the connection. Good to see it in print before
upgrading a critical legacy app.

Thanks Stephen.
"Stephen Howe" wrote:

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: 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: 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: 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: 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)
  • Help! Dont understand transactions
    ... I recently decided to add transaction control to some operations on an app I am developing. ... I started by doing a test web page with an update of up to 3 different tables driven by simple pushbuttons, and more buttonds for BEGIN TRAN, COMMIT and ROLLBACK. ... It did exactly what I wanted, and even though I was using separate record set variable for each of the commands and tables, and closing my connection after every BEGIN TRAN. ...
    (microsoft.public.sqlserver.clients)