Fellow Developers,

I am receiving an error trying to commit a transaction using DAO with
VB5.0.   Apparently, VB says I am trying to commit without a "begin
transaction" statement.  However, I do have the begin transaction
statement!  Any ideas as to why VB doesn't see this?  (In the code below, I
get the error the FIRST time I try to commit in the inner loop so I should
not have nested transactions.)

Thanks for any advice!

Shawn Woods

Dim wks As Workspace
dim db As Database
Dim try As Integer, probLoopCnt As Integer
Dim rs As Recordset
Dim qd As QueryDef
.
.
.
On Error GoTo Failure
For probLoopCnt = 0 To numberProbs - 1
        For try = 1 To 3
                Set wks = DBEngine.Workspaces(0)
                wks.BeginTrans    '<<<<<<-------- begin transaction
                Set rs = Nothing
                Set rs = db.OpenRecordset("<Select Statement on Access Table>")
                With rs
                        If .RecordCount <> 0 Then
                                        Do Until .EOF
                                        <execute a couple of Access QueryDefs here>
                                        .MoveNext
                                Loop
                        End If
                End With
                <Access QueryDef here (non-contributary to the problem)>
                wks.CommitTrans '<<<<<<-------- commit transaction (dies FIRST time it
hits this command)
                Exit For
Failure:
                wks.RollBack
                Resume NextTry
NextTry:
        Next try
Next probLoopCnt

Hello Shawn

When you open or close a recordset in a workspace any pending transactions
are immediatly processed.  In your code, you are starting a transaction,
then opening a recordset thus closing the pending transaction.  Reverse the
operation, and see if that works for you. First open any relevant
recordsets, then begin the transaction.  Commit the transaction, then close
the recordsets.  Also, some ISAM databases will not work with transactions
just in case you happen to be using Paradox tables or something like that
:-)

--
Ibrahim
A MS Developer MVP
====================
Malluf Consulting Services

http://www.*-*-*.com/
===================================
Check out the latest ViewSource column:
http://www.*-*-*.com/
===================================
My {*filter*}: http://www.*-*-*.com/

Ibrahim,

Thanks for the help!  It makes perfect sense to me now and everything is
working just fine -- Thank you!

Shawn Woods