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

From: david epsom dot com dot au (david_at_epsomdotcomdotau)
Date: 02/24/05 

Date: Thu, 24 Feb 2005 20:27:42 +1100

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: 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)
  • Re: "cant open any more tables"
    ... Assign the workspace variable, then the transaction, ... If you do need to close a recordset, ... >> updates every time through the loop. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Testing for rs.AddNew
    ... transaction until lock is released on Commit or RollBack. ... Otherwise just read ADO help about rules of AddNew method, ... Update method implicitly and CancelUpdate will not work. ... because there is no that recordset anymore. ...
    (microsoft.public.vb.database)