Rollback doesn't work..... Why???

[cariengon]

I've set up a transaction so that if anything happens, it will return to the state prior to the start of the transaction. What I'm expecting, is if there is an error (or the user hits the ESC key) the transaction will rollback.

When I've tested this, the rollback doesn't work. For example, the first SQLstmt below will run and when it starts the second, it may error. I would expect that the rollback would make the INSERT into statement reversed so that it is like nothing happened? All that happens now is that it errors, and follows the steps to exit sub.

Is there something I'm missing in my code? Do rollbacks not work if you've completed a query?

Help...

Thanks,
Carie



*************************

Private Sub cmdSettlePOs1_Click()
On Error GoTo SettlePOs1_Err

'Set up Transaction
Dim wrk As DAO.Workspace
Dim fInTrans As Boolean
fInTrans = False
Set wrk = DAO.Workspaces(0)

Dim intSettledCount As Long
Dim intLoopCount As Long
Dim intCountofBU As Long
Dim SQLstmt As String

DoCmd.OpenForm "20000_ProcessRunning_frm", acNormal

'Process 2021 Query Set

wrk.BeginTrans
fInTrans = True

intSettledCount = 1 'SetCounters
intLoopCount = 0
intCountofBU = 0

DoCmd.SetWarnings False

Do While intSettledCount <> 0

SQLstmt = &quot;INSERT INTO [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl] ( [CountOfBU], [BU], [PO Num], [Trans Key], [Inv Num], [Inv Ln], [SumOfTrans Amt] ) &quot; & _
&quot;SELECT Count([100_MAIN_tbl].[BU]) AS [CountOfBU], [100_MAIN_tbl].[BU], [100_MAIN_tbl].[PO Num], [100_MAIN_tbl].[Trans Key], [100_MAIN_tbl].[Inv Num], &quot; & _
&quot;[100_MAIN_tbl].[Inv Ln], Sum([100_MAIN_tbl].[Trans Amt]) AS [SumOfTrans Amt] FROM [100_MAIN_tbl]&quot; & _
&quot;GROUP BY [100_MAIN_tbl].[BU], [100_MAIN_tbl].[PO Num], [100_MAIN_tbl].[Trans Key], [100_MAIN_tbl].[Inv Num], [100_MAIN_tbl].[Inv Ln], [100_MAIN_tbl].[Status] &quot; & _
&quot;HAVING ((([100_MAIN_tbl].[BU]) Is Not Null) AND (([100_MAIN_tbl].[PO Num]) Is Not Null) AND (([100_MAIN_tbl].[Trans Key]) Is Not Null) AND (([100_MAIN_tbl].[Inv Num]) Is Not Null) &quot; & _
&quot;AND (([100_MAIN_tbl].[Inv Ln]) Is Not Null) AND ((Sum([100_MAIN_tbl].[Trans Amt]))=0) AND (([100_MAIN_tbl].[Status]) Is Null))&quot;
DoCmd.RunSQL SQLstmt

If IsNull(DSum(&quot;CountOfBU&quot;, &quot;2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl&quot) Then
intSettledCount = 0
Else: intCountofBU = DSum(&quot;CountOfBU&quot;, &quot;2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl&quot
intSettledCount = intCountofBU
End If

If intSettledCount = 0 Then Exit Do

SQLstmt = &quot;UPDATE [100_MAIN_tbl] INNER JOIN [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl] &quot; & _
&quot;ON ([100_MAIN_tbl].[Trans Key] = [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl].[Trans Key]) &quot; & _
&quot;AND ([100_MAIN_tbl].[Inv Ln] = [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl].[Inv Ln]) &quot; & _
&quot;AND ([100_MAIN_tbl].[Inv Num] = [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl].[Inv Num]) &quot; & _
&quot;AND ([100_MAIN_tbl].BU = [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl].BU) &quot; & _
&quot;AND ([100_MAIN_tbl].[PO Num] = [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl].[PO Num]) &quot; & _
&quot;SET [100_MAIN_tbl].Status = 'Settled', [100_MAIN_tbl].[Matched Primary Key] = Now() & ' - BU, PO, TransKey, InvNum, & InvLn Net to 0 '&quot; & _
&quot;WHERE ((([100_MAIN_tbl].Status) Is Null) AND (([100_MAIN_tbl].BU) Is Not Null))&quot;
DoCmd.RunSQL SQLstmt

DoCmd.RunSQL &quot;DELETE * From [2021_BU_PO_TransKey_InvNum_InvLn_Net0_tbl]&quot;

intLoopCount = intLoopCount + 1

'Check to make sure transactions matched properly
Me.TotalNETSettled = DSum(&quot;Net&quot;, &quot;401_Check_Settled_100_Table_Totals&quot
If IsNull(Me.TotalNETSettled) Then
Me.TotalNETSettled = 0
End If
If Me.TotalNETSettled <> 0 Then
wrk.Rollback
MsgBox &quot;The transactions matched did not equal zero. Please verify that there are no NULL values in the Trans Amt field.&quot; & vbCrLf & vbCrLf & _
&quot;The matching has been cancelled.&quot;, vbOKOnly, &quot;Matching Error&quot;
Forms![2002_SettlePOs]![401_Check_Settled_100_Table_Totals_sfm].Requery
Me.TotalRecordsSettled = DSum(&quot;CountofBU&quot;, &quot;401_Check_Settled_100_Table_Totals&quot
Me.TotalNETSettled = DSum(&quot;Net&quot;, &quot;401_Check_Settled_100_Table_Totals&quot
If IsNull(Me.TotalNETSettled) Then
Me.TotalNETSettled = 0
End If
DoCmd.Close acForm, &quot;20000_ProcessRunning_frm&quot;
Exit Sub
End If

Loop

wrk.CommitTrans
fInTrans = False

DoCmd.SetWarnings True

'Requery Subform
Forms![2002_SettlePOs]![401_Check_Settled_100_Table_Totals_sfm].Requery
Me.TotalRecordsSettled = DSum(&quot;CountofBU&quot;, &quot;401_Check_Settled_100_Table_Totals&quot
Me.TotalNETSettled = DSum(&quot;Net&quot;, &quot;401_Check_Settled_100_Table_Totals&quot
If IsNull(Me.TotalNETSettled) Then
Me.TotalNETSettled = 0
End If

MsgBox &quot;Process Complete!&quot; & vbCrLf & vbCrLf & _
&quot;Depending on the number of records processed, you may want to&quot; & vbCrLf & _
&quot;'Move Settled Transactions' and Compact & Repair the database.&quot;, vbOKOnly, &quot;&quot;
DoCmd.Close acForm, &quot;20000_ProcessRunning_frm&quot;

Exit Sub

SettlePOs1_Exit:
DoCmd.SetWarnings True
DoCmd.Close acForm, &quot;20000_ProcessRunning_frm&quot;
Exit Sub

SettlePOs1_Err:
If fInTrans Then
wrk.Rollback
End If
MsgBox Err.Number & &quot;: &quot; & Err.Source & Err.Description
Resume SettlePOs1_Exit

End Sub
***********************************

 

[vbajock]

I think your problem may be in

DoCmd.RunSQL SQLstmt

the rollback stuff are methods of the database object in DAO, so I think

wrk.execute Sqlstmt

will give you the rollback you are looking for.

BTW - ADO gives you a much simpler way to control rollbacks through the ADO connection object.