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

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%(E-Mail Removed)...
> '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)
>
>

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)

>-----Original Message-----
>'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)
>
>
>.
>

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


<(E-Mail Removed)> wrote in message
news:08c901c51819$f91921b0$(E-Mail Removed)...
> 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)
>
>>-----Original Message-----
>>'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)
>>
>>
>>.
>>

'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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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%(E-Mail Removed)...
>> '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)
>>
>>
>
>

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://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:(E-Mail Removed)...
> ...Links (to separate databases
> 'for each 'Set' statement) have the same problem as local tables.

David,

I found an old KB article that describes something similar in VB
(http://support.microsoft.com/default...b;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" <david@epsomdotcomdotau> wrote in message
news:(E-Mail Removed)...
> '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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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%(E-Mail Removed)...
>>> '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)
>>>
>>>
>>
>>
>
>

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)


"Allen Browne" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
> news:(E-Mail Removed)...
>> ...Links (to separate databases
>> 'for each 'Set' statement) have the same problem as local tables.
>
>

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/WileyT...764559036.html

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:(E-Mail Removed)...
> '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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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%(E-Mail Removed)...
>>> '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)
>>>
>>>
>>
>>
>
>

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/WileyT...764559036.html

"david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
news:e%(E-Mail Removed)...
> 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)
>
>
> "Allen Browne" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>> 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://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "david epsom dot com dot au" <david@epsomdotcomdotau> wrote in message
>> news:(E-Mail Removed)...
>>> ...Links (to separate databases
>>> 'for each 'Set' statement) have the same problem as local tables.
>>
>>
>
>