commit or rollback a transaction without first beginning a transaction

[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


'note that this does not cause an error:
DBEngine(0).BeginTrans
DBEngine(0).CommitTrans

'same or different tables: snapshot or dynaset: Access 2000


(david)

 

[Graham R Seach]

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

 

[Guest]

hi,
you got it out of sequence it looks like.
you have got the create record set part between the begin
and commit trans.
here is one way i use recordsets. a little different
terminology from your's but mine works. at least at
inventory time.

Set db = CodeDb()
Set rsTAG = db.OpenRecordset("qryTAGNums", dbOpenDynaset)
Set rsBOM = db.OpenRecordset("TempTAG", dbOpenDynaset)
rsBOM.MoveFirst
rsTAG.MoveFirst
BeginTrans
Do Until rsBOM.EOF
rsBOM.Edit
rsBOM!TagID = rsTAG!TAG_TagID
rsBOM.Update

rsBOM.MoveNext
rsTAG.MoveNext
Loop
CommitTrans

rsTAG.Close
rsBOM.Close

what is this doing? it is creating 2 record sets,
assigning existing inventory tag numbers to bills of
material in a temp table then appending the temp table to
the inventory tag table.(I left out some of the code. the
sub has 169 lines of it all total. a lot of qualifing and
error handling, before and after)

 

[david epsom dot com dot au]

you got it out of sequence it looks like.

It's a sample demonstration piece of code. The original
code uses a separate function with a statically declared
recordset to avoid the overhead of repeatedly opening
and closing, but the service function needs the flexibility
to close and re-open the recordset in some cases.

(david)

 

[david epsom dot com dot au]

'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.

 

[Allen Browne]

David there are some quite strange things that can happen if you are opening
separate database or workspaces and running transactions.

It is quite possible that dbEngine(0)(0) is no longer the default database
after this kind of operation, i.e. Access may reassign them in a different
order, so the default workspace could become dbEngine(0)(1) for example.

May not be related to the issue you are describing, but thought you would
like to be alerted to the possibilty.

 

[Graham R Seach]

David,

I found an old KB article that describes something similar in VB
(http://support.microsoft.com/default.aspx?scid=kb;en-us;110722).

If you apply the same kind of logic to VBA, it seems to make sense.

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

 

[david epsom dot com dot au]

Although I know that dbEngine(0)(0) may not be CurrentDB
(because the CurrentDB has been loaded after another DB),
I am not aware of a situation where dbEngine(0) is anything
other than the default workspace. Might it happen using
the new feature which allows you to change workgroup from
within Access?

This is partly old (Access 2.0) code, so there a couple
of things we would probably do differently now, but I
don't know of any way to refer to the default workspace
other than Application.dbengine(0)

 

[Graham R Seach]

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

 

[Graham R Seach]

David,

I'm not going to bet my life on it, but I guess it is theoretically possible
for DBEngine(0) to be something other than #Default Workspace#. After all,
DBEngine.Workspaces is a collection, just like the Databases collection, and
we all know it's possible for DBEngine(0)(0) to be something other than the
default. Since (as far as I know) all CurrentDb does is call
DBEngine(0).Databases.Refresh, you could do the same kind of thing before
calling DBEngine(0) or DBEngine(0)(0). That doesn't solve your current
problem, but it may help clarify DBEngine a bit.

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

 

[Allen Browne]

Graham looks like he is on track for trouble-shooting this particular issue
for you.

The particular problem I was referring to is discussed in this thread:

http://groups-beta.google.com/group..._doneTitle=Back+to+Search&&d#9ef472d3db4c52a3

 

[david epsom dot com dot au]

Allan: that entire thread is a discussion of the use of
dbEngine(0)(0) (which used to be a common way to reference
CurrentDB).

I am not using dbEngine(0)(0). Nor am I using database
level transactions at all: they were depreciated with
the introduction of workspace transactions.

Incidentally, the url you quote may be more compactly
expressed as
http://groups-beta.google.com/group...read/thread/7c7c883f2d5e2af5/9ef472d3db4c52a3

(my news client will line wrap that line)

Everything after the first ? in your quoted url relates to the
query you used to find the thread, not the thread itself.

(david)

 

[david epsom dot com dot au]

default. Since (as far as I know) all CurrentDb does is call

DBEngine(0).Databases.Refresh, you could do the same kind of

I didn't know that So you think that if I use CurrentDB
before using Application.dbEngine(0)(0), dbEngine(0)(0) is
likely to be restored?

I never use dbEngine(0)(0) (most of my code is written against
CodeDB anyway), and I never use Wizards, or compact while coding,
so I've never seen the problem or worried about it.

(david)

 

[david epsom dot com dot au]

Unfortunately, the suggested work around (closing the Db object
to avoid the implicit rollback) doesn't help here.

My original problem came from a logic error: the recordset
was closed and re-opened if it had moved to the final record
(.eof = true). Since this was not common, the problem did
not appear in use or on test until long after the code was
written.

(david)

 

[david epsom dot com dot au]

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]

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]

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.

Having fixed the logic error which caused it to fail in test,
I am not likely to have the recordset close or open at all,
and quite unlikely to have it close or open while inside a
transaction. But 'unlikely' is not as good as an absolute,
and I wish I had a better solution.

I don't know of any way to check if I am inside a transaction, ?????
I need a way to refresh the recordset if it has been updated,
and I liked the fact that on 'refresh' it actually re-opened
the recordset, which did not make any assumptions about the
state of the existing recordset.

My better solution is to create a separate static workspace for
the statically loaded recordset, but the whole exercise makes me
less enthusiastic about converting the other data caches (collection
or array based) over to simple static recordsets.

 

[Graham R Seach]

David,

I'm glad to hear you were at least able to get something that works, but I'm
still curious about *why* it errors out. I'm trying to find out, but it may
take a little time. The interesting thing that was pointed out by another
MVP was that the second recordset is actually invalid (evidenced by any
attempt to navigate the cursor). The reason for *that* is still unknown,
however.

As far as I can determine, there is no way to detect whether you're inside a
transaction, or to count transactions. I'm certain there is an internal
flag/counter - but it mustn't be exposed to VB.

As for refreshing the recordset, the Recordset object has a Requery method,
which might suit your purposes. But I still recommend explicitly closing
recordsets when they are no longer required, or when you need to re-open
them with different parameters. It's not good practice to open a recordset
twice without closing it between instantiations.

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