Solved Error 3034: You tried to commit or rollback a transaction without first beginning a transaction. (1 Viewer)

Mike Krailo

Active member
Local time
Yesterday, 22:08
Joined
Mar 28, 2020
Messages
158
Having a bit of trouble locating the source of this error. It always goes right to the DAO.DBEngine.Rollback line complaining about not starting a transaction when after stepping through the code, it sure does start the transaction. If I skip over that line in the debugger, everything appears to have updated correctly. Anyone know what could be causing the issue? All the code complies fine.

Code:
Private Sub MovePortBtn2_Click()
   Dim StrSQL As String
 
   TempVars!DestPortNum = ""
   If IsNull(Me.AvailablePortsCbo) Then
      MsgBox "No destination port was selected, please select a port from the dropdown."
      Exit Sub
   End If
 
   TempVars!DestPortNum = Me.AvailablePortsCbo.Column(1)
   TempVars!DestPortID = Me.AvailablePortsCbo.Column(0)
   If Not MyYesNoBox("About to move port " & TempVars!OrigPortNum & " to port " & TempVars!DestPortNum) Then
      GoTo CleanUp
   End If
      DAO.DBEngine.BeginTrans
      On Error GoTo TransErr
     
      StrSQL = "Update Port SET [PhyStatus]='BAD', [SwStatus]='DISABLED', [VLan]=666, [VName]='DUMMY', [ConnTo]='Moved to Port " &               TempVars!DestPortNum _
                     & "' WHERE PortID= " & TempVars!OrigPortID
'      Debug.Print StrSQL
     
      ' Update the selected port to BAD and put it on the DUMMY vlan of 666
      CurrentDb.Execute StrSQL, dbFailOnError
     
      StrSQL = "Update Port SET [PhyStatus]='USED', [SwStatus]='ENABLED', [VLan]=" & TempVars!OrigVLan & ", [VName]='" & TempVars!OrigVName _
                     & "', [ConnTo]='" & TempVars!OrigConnTo & "' WHERE PortID= " & TempVars!DestPortID
'      Debug.Print StrSQL
     
      ' Update the destination port to the orginal values that the BAD port had
      CurrentDb.Execute StrSQL, dbFailOnError
     
     
      DAO.DBEngine.CommitTrans
      MsgBox "Successfull! Port number " & Me!PNum & " moved to " _
                     & TempVars!DestPortNum & vbCrLf & "Notes also updated. "
     
      DoCmd.Close acForm, "MovePort"
      Exit Sub
     
CleanUp:
   ' Remove TempVars
   TempVars.Remove "DestPortNum"
   TempVars.Remove "OrigConnTo"
   TempVars.Remove "OrigPortID"
   TempVars.Remove "OrigVLan"
   TempVars.Remove "OrigVName"
   TempVars.Remove "DestPortNum"
 
   Exit Sub
   
TransErr:
   DAO.DBEngine.Rollback
   MsgBox "Transaction failed. Error: " & Err.Description
   DoCmd.Close acForm, "MovePort"
End Sub
 

arnelgp

error reading drive A:
Local time
Today, 11:08
Joined
May 7, 2009
Messages
11,183
you need to create a Workspace variable and instantiate the Transaction there:

Dim ws As DAO.WorkSpace

Private Sub MovePortBtn2_Click()

Set ws = DbEngine.Workspace(0) 'the default workspace

ws.BeginTrans
ws.CommitTrans
ws.Rollback
 

Mike Krailo

Active member
Local time
Yesterday, 22:08
Joined
Mar 28, 2020
Messages
158
Ok I'll try that in the morning. Gotta get up early tomorrow.
 

arnelgp

error reading drive A:
Local time
Today, 11:08
Joined
May 7, 2009
Messages
11,183
ok, don't forget the pajamas!
 

Mike Krailo

Active member
Local time
Yesterday, 22:08
Joined
Mar 28, 2020
Messages
158
Maybe you meant Set ws = DbEngine.Workspaces(0)
The singular version of Workspace didn't compile. In any case, I'm still getting the exact same error. This is so frustrating.
 

MajP

You've got your good things, and you've got mine.
Local time
Yesterday, 22:08
Joined
May 21, 2018
Messages
4,154
Any chance you throw the error after the commit. Your error trap would try a rollback. Need to move your message box before the rollback
 

Mike Krailo

Active member
Local time
Yesterday, 22:08
Joined
Mar 28, 2020
Messages
158
Thanks MajP, that helped me find the error. Moving the msgbox up over the rollback gave me an error pointing back to that msgbox line itself. The reference to Me!PNum was incorrect, changed to TempVars!OrigPortNum and everything works properly now.
 

Users who are viewing this thread

Top Bottom