Transactions and queries

[tmyers]

I wasn't able to find a straight forward answer to this so am hoping one of you could answer it before I invest time in something that wont work .

When dealing with DAO and transactions, if I have some update queries run in the loop, if for whatever reason the process fails, will what the queries did also be rolled back? Can you even have them within the transaction/loop? Currently I am just wanting to use docmd.openquery.

 

[sonic8]

You (probably) cannot use DoCmd.... to execute queries in transactions because you haven't enough control over the database/workspace in which the query is executed. You need to use the DAO objects instead.
See How to Use Transactions in Microsoft Access for further information.

 

[tmyers]

I was thinking that it was unlikely that docmd would work.
I can easily break the queries into strings and do database.execute. Actually, that would probably be the more preferable way as I can pass variables into the queries more easily to get what I am after.

 

[sonic8]

I can easily break the queries into strings and do database.execute. Actually, that would probably be the more preferable way as I can pass variables into the queries more easily to get what I am after.

You can also use stored queries with transactions by either using a QueryDef object or by passing the name of the query to database.Execute.
However, references to controls in Access forms in the queries themselves might not work and need to be passed into the query explicitly.

 

[tmyers]

For argument sake, here is the full module that I added the query into. Was a super simple query but was unsure how it would interact within the workspace/transaction environment.

Dim rs          As DAO.Recordset
Dim Crs         As DAO.Recordset
Dim wrk         As DAO.Workspace
Dim SID         As Long 'Variable for SingleID
Dim r           As Long 'variable for ReelID
Dim ID          As Long 'variable for ticketID
Dim CurL        As Long 'variable for current length
Dim CutL        As Long 'variable for cut length
Dim CutN        As Long 'variable for number of cuts
Dim nCut        As Long 'variable for total cut length
Dim strsql      As String


On Error GoTo ErrorHandler

ID = [Forms]![frmWireRoom]![TicketID]
'get recordset from singles table
Set wrk = DBEngine.Workspaces(0)
Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblSingleCut WHERE TicketID = " & ID & "")

With rs
    wrk.BeginTrans
    Do While Not .EOF
        SID = ![SingleID]
        r = ![WireReelID]
        CutL = ![CutLength]
        CutN = ![CutNum]
        If IsNull(CutN) Then
            nCut = CutL
        End If
        nCut = CutL * CutN
        'set second recordset to update reels
        Set Crs = CurrentDb.OpenRecordset("SELECT * FROM tblWireRoom WHERE [ReelID] = " & r & "")
        With Crs
            If ![ReelID] = r Then
                Crs.Edit
                CurL = ![CurrentLength]
                Crs!CurrentLength = CurL - nCut
                Crs.Update
            End If
        End With
        With rs
            If ![SingleID] = SID Then
                rs.Edit
                ![SingleComplete] = True
                rs.Update
            End If
        End With
        
        strsql = "INSERT INTO tblCutLog ( CutReelID, StartingLength, CutLength, EndLength, TicketID) "
        strsql = strsql & "VALUES (" & r & ", " & CurL & ", " & nCut & ", " & CurL - nCut & ", " & ID & ");"
        CurrentDb.Execute strsql, dbFailOnError
        .MoveNext
    Loop
End With

wrk.CommitTrans

ErrorHandler:
Select Case Err.Number
    Case 3317
        MsgBox "One of the choosen source(s) has insufficient length. Please choose a different source. Operation has been canacelled and no changes have been made", vbOKOnly, "Error"
        wrk.Rollback
        Exit Sub
End Select