commit or rollback a transaction without first beginning a transaction

commit or rollback a transaction without first beginning a transaction

Post by david epso » Tue, 22 Feb 2005 17:15:14


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

commit or rollback a transaction without first beginning a transaction

Post by Graham R S » Tue, 22 Feb 2005 22:18:43

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

 
 
 

commit or rollback a transaction without first beginning a transaction

Post by anonymou » Tue, 22 Feb 2005 22:33:48

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)

dao.dbOpenSnapshot)
dao.dbOpenSnapshot)
2000
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Wed, 23 Feb 2005 07:34:25

> 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)
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Thu, 24 Feb 2005 09:46:33

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

commit or rollback a transaction without first beginning a transaction

Post by Allen Brow » Thu, 24 Feb 2005 13:05:42

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by Graham R S » Thu, 24 Feb 2005 22:11:44

David,

I found an old KB article that describes something similar in VB
( http://www.yqcomputer.com/ ;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
---------------------------
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Fri, 25 Feb 2005 09:07:29

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)
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by Graham R S » Fri, 25 Feb 2005 09:29:56

avid,

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: XXXX@XXXXX.COM ...


 
 
 

commit or rollback a transaction without first beginning a transaction

Post by Graham R S » Fri, 25 Feb 2005 09:42:38

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.yqcomputer.com/
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by Allen Brow » Fri, 25 Feb 2005 10:50:35

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://www.yqcomputer.com/ (0)(0)+group:microsoft.public.access.*+author:Allen+author:Browne&_done=%2Fgroups%3Fas_q%3DdbEngine(0)(0)%26num%3D50%26scoring%3Dr%26hl%3Den%26ie%3DUTF-8%26as_epq%3D%26as_oq%3D%26as_eq%3D%26as_ugroup%3Dmicrosoft.public.access.*%26as_usubject%3D%26as_uauthors%3DAllen+Browne%26lr%3D%26as_qdr%3D%26as_drrb%3Db%26as_mind%3D1%26as_minm%3D1%26as_miny%3D1998%26as_maxd%3D23%26as_maxm%3D12%26as_maxy%3D2004%26safe%3Doff%26&_doneTitle=Back+to+Search&&d#9ef472d3db4c52a3

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://www.yqcomputer.com/
Reply to group, rather than allenbrowne at mvps dot org.
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Fri, 25 Feb 2005 17:01:28

llan: 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/microsoft.public.access.modulesdaovba/browse_thread/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)



"Allen Browne" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...


 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Fri, 25 Feb 2005 17:57:13

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

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)
 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Fri, 25 Feb 2005 18:03:25

nfortunately, 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)

"Graham R Seach" < XXXX@XXXXX.COM > wrote in message
news:% XXXX@XXXXX.COM ...


 
 
 

commit or rollback a transaction without first beginning a transaction

Post by david epso » Fri, 25 Feb 2005 18:27:42

hat 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" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...