That is a very heavy piece of text. I think that it suggests
what happens with a Jet recordset. Neither does rollback.
when connected to an ODBC data source.
a statically open recordset, which is searched using FindFirst.
a hundred records here), if the table is already open.
this way.
Post by Graham R SeachDavid,
Here's what I've found. It's not much, so don't get excited.
"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
Post by david epsom dot com dot auSet db = CodeDb
Set rs = db.OpenRecordset("tblADMIN_Currency", dao.dbOpenSnapshot)
DBEngine(0).BeginTrans
rs.close
DBEngine(0).CommitTrans
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.
Post by Graham R SeachDavid,
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
---------------------------
Post by david epsom dot com dot au'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
DBEngine(0).BeginTrans
DBEngine(0).CommitTrans
'same or different tables: snapshot or dynaset: Access 2000
(david)