BeginTrans/CommitTrans/Rollback -Help please

[Binnit]

I am trying to use the above method and have the following in an A97 form module:-

Private Sub cmdImportfolios_Click()
Dim Wks As Workspace
Set Wks = DBEngine.Workspaces(0)
    

Me.Recalc
DoCmd.SetWarnings False

If MsgBox("Upload new folio data", vbYesNo + vbDefaultButton2, "Upload Data") = vbYes Then
[b]
Wks.BeginTrans
[/b]
'Empty Import Table ready for new data
DoCmd.RunSQL "Delete * from tblFolioImport"

'Add existing folio data to History table
DoCmd.OpenQuery "qryAppendFolioDetailsToHistory"

'Delete existing folios from folio table following archiving in the History table
DoCmd.OpenQuery "qryDeleteFoliosFromFolioDetailsTable"

'Transfer Spreadsheet in to empty Folio Import table
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, _
"tblFolioImport", "NewFolios.xls", -1

'Add new imported items to Main Folio Details
DoCmd.OpenQuery "qryAppendImportFolioToFolioDetails"

DoCmd.OpenTable "tblfolioimport", acViewPreview

If MsgBox("Import Complete" & vbCr + vbCr & "Is this the correct data?", vbYesNo + vbInformation, "Spreadsheet Successfully Imported") = vbYes Then
[b]
Wks.CommitTrans
[/b]
Else
MsgBox "Upload aborted", vbOKOnly, "No data uploaded"
[b]
Wks.Rollback
[/b]
End If

Set Wks = Nothing
DoCmd.SetWarnings True
End If

'Change focus prior disabling command
Me.txtPolPolicyno.SetFocus
Me.cmdImportfolios.Enabled = False
Me.Recalc
End Sub

This does not seem to work properly as all the updates are committed even when the option to abort is selected. Anyone able to advise if this is being used in the correct context or am I just missing something?

Thanks in advance

If IT ain’t working Binnit and Reboot

 

[JerryKlmns]

Binnit,

I don`t know if the reason is that you use the DoCmd.OpenQuery method, but have you tried to use recordsets that do what those queries are supposed to do?