Re: commit or rollback a transaction without first beginning a transaction

From: Graham R Seach (gseach_at_NOSPAMpacificdb.com.au)
Date: 02/24/05 

Date: Fri, 25 Feb 2005 00:24:47 +1100

David,

<<I think that it suggests that Commit will destroy an open recordset?>>
That wasn't my take on it. I understood the opposite; that when a recordset
(or more accurately its connection) closes, Jet terminates all open
transactions in that workspace. That'd be why you get the error you're
getting.

Even so, I think to resolve your issue, you need to either explicitly close
the recordset before entering the transaction, or re-open it before opening
the transaction.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:eqTEYMlGFHA.904@tk2msftngp13.phx.gbl...
> That is a very heavy piece of text. I think that it suggests
> that Commit will destroy an open recordset? That is not
> what happens with a Jet recordset. Neither does rollback.
>
> However, it does suggest another possible error condition
> when connected to an ODBC data source.
>
> This is one of the few places in the code that maintains
> a statically open recordset, which is searched using FindFirst.
> FindFirst is fast enough on small tables (often less than
> a hundred records here), if the table is already open.
>
> All of the other cached data is cached in arrays or
> collections, precisely to avoid unexpected problems
> from having open recordsets, but it is harder to do a
> multi-field search against a collection, and certainly
> requires a lot more code, so we thought we'd try it
> this way.
>
> (david)
>
>
>
>
>
> "Graham R Seach" <gseach@NOSPAM_pacificdb.com.au> wrote in message
> news:OwLRpagGFHA.2936@TK2MSFTNGP15.phx.gbl...
>> David,
>>
>> Here's what I've found. It's not much, so don't get excited.
>>
>> Excerpt from the Jet Programmer's Guide:
>>
>> "When a transaction is explicitly started (by calling SQLSetConnectOption
>> with the SQL_AUTOCOMMIT fOption argument set to SQL_AUTOCOMMIT_OFF), the
>> SQLTransact function is called to commit or roll back the transaction.
>> The Microsoft Access driver supports multiple active statement handles on
>> a single connection, so when SQLTransact is called, all statements on the
>> connection are either committed or rolled back."
>>
>> "All open cursors on all hstmt arguments associated with the hdbc
>> argument are closed when the transaction is committed or roleld back.
>> SQLTransact leaves any hstmt argument present in a prepared state if the
>> statement was prepared, or in an allocated state if it was executed
>> directly. Closing all cursors can have unforeseen consequences. For
>> example, suppose an application has two active statements within an
>> explicit transaction; one statement in which an UPDATE statement was
>> executed, and another statement in which a SELECT statement was executed
>> and then SQLExtendedFetch called to return a recordset. If SQLTransact is
>> called to commit the update, all the operations performed by the UPDATE
>> statement on the first statement handle are committed (as expected), but
>> in addition, the recordset generated by SQLExtendedFetch is deleted,
>> because the cursor on the second statement handle is closed."
>>
>> "The Microsoft Access driver supports active transactions. Because
>> transactions are associated with a connection in ODBC, each transaction
>> must be on a different connection (hdbc argument). Because nested
>> transactions, which are supported natively by the Microsoft Jet engine,
>> are not supported in ODBC, they are not available through the Desktop
>> Database Drivers."
>>
>> =====
>> This suggests to me that when you open the second recordset inside the
>> explicit transaction, Jet implicitly closes the (already open) recordset
>> before re-opening it. In the process, it rolls back all open
>> transactions. This behaviour is normally associated with databases, but
>> when you remember than Jet automatically creates pessimistic locks
>> whenever an explicit transaction is opened, I think it might also apply
>> to the recordset in this case, despite it being a snapshot.
>>
>> Regards,
>> Graham R Seach
>> Microsoft Access MVP
>> Sydney, Australia
>>
>> Microsoft Access 2003 VBA Programmer's Reference
>> http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
>>
>> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
>> news:e4crgEUGFHA.3908@TK2MSFTNGP12.phx.gbl...
>>> 'This fails also, but with an explicit error on the close:
>>>
>>> Set db = CodeDb
>>> Set rs = db.OpenRecordset("tblADMIN_Currency", dao.dbOpenSnapshot)
>>>
>>> DBEngine(0).BeginTrans
>>> rs.close
>>> DBEngine(0).CommitTrans
>>>
>>> 'This works by itself:
>>> DBEngine(0).BeginTrans
>>> Set rs = db.OpenRecordset("tblADMIN_Currency", dao.dbOpenSnapshot)
>>> rs.close
>>> DBEngine(0).CommitTrans
>>>
>>> 'So it appears that closing the recordset (done implicitly in the
>>> 'first code sample) is where the problem lies, but it is only a
>>> 'problem if you opened a recordset outside the transaction! I've
>>> 'recreated the same problem in A97. Links (to separate databases
>>> 'for each 'Set' statement) have the same problem as local tables.
>>>
>>>
>>> "Graham R Seach" <gseach@NOSPAMpacificdb.com.au> wrote in message
>>> news:eFaTjfBGFHA.2296@TK2MSFTNGP15.phx.gbl...
>>>> David,
>>>>
>>>> Not 100% sure, but my guess would be that the transaction fails to open
>>>> because it can't create a lock on the table. The first recordset locked
>>>> the table first. If you omit or explicitly close the first recordset,
>>>> the error goes away.
>>>>
>>>> I know it's a snapshot, but its the only thing I can think of, and
>>>> given the evidence (by omitting or closing the first recordset), it
>>>> would seem to make a weird kind of sense. You've piqued my interest
>>>> now; I'll check my Jet book in the morning.
>>>>
>>>> Regards,
>>>> Graham R Seach
>>>> Microsoft Access MVP
>>>> Sydney, Australia
>>>> ---------------------------
>>>>
>>>> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
>>>> news:eItw11%23FFHA.2180@TK2MSFTNGP12.phx.gbl...
>>>>> 'Why does this code cause an error?
>>>>>
>>>>> Dim db As dao.Database
>>>>> Dim rs As dao.Recordset
>>>>>
>>>>> Set db = CodeDb
>>>>> Set rs = db.OpenRecordset("tblADMIN_Currency", dao.dbOpenSnapshot)
>>>>>
>>>>> DBEngine(0).BeginTrans
>>>>> Set rs = db.OpenRecordset("tblADMIN_Currency", dao.dbOpenSnapshot)
>>>>> DBEngine(0).CommitTrans
>>>>>
>>>>>
>>>>> 'note that this does not cause an error:
>>>>> DBEngine(0).BeginTrans
>>>>> DBEngine(0).CommitTrans
>>>>>
>>>>> 'same or different tables: snapshot or dynaset: Access 2000
>>>>>
>>>>>
>>>>> (david)
>>>>>
>>>>>
>>>>
>>>>
>>>
>>>
>>
>>
>
>

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: msjtes40.dll - IIS Crash
    ... must use extremely good coding practices to make it work. ... Jet is capable of. ... Don't open a connection until immediately before you intend to use ... a recordset, leaving the connection open until the page expires, then ...
    (microsoft.public.inetserver.asp.db)
  • 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)