Why rollback not working in Access linked to sharepoint list

I'm trying to insert data from Excel into an Access table that is linked to a Sharepoint list. I got this working, except for the fact that my transaction is not working. The new item always shows up in Sharepoint immediately after the rs.Update line. And the line ws.Rollback does nothing. Can anyone explain why this is happening and can this be made to work? The code:

Private Sub InsertData()
    
    
    Dim dbConnection As New ADODB.Connection
    Dim dbCommand As New ADODB.Command
    Dim ws As DAO.Workspace
    Dim rs As DAO.Recordset2
    Dim db As DAO.Database
    Dim sowItem As clsSowingEntry
    Dim crit As String
    Dim dKey As Variant
    Dim rsEvent As DAO.Recordset2
    
    On Error GoTo errHandler
    
    If datadict.Count = 0 Then
        Set datadict = Nothing
        MsgBox ("No valid sowing events were found.")
        End
    End If
    
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase(accessPath)
    Set rs = db.OpenRecordset("FormData")
    Set rsEvent = db.OpenRecordset("EventType")
            
    ws.BeginTrans
    For Each dKey In datadict.Keys
    
        Set sowItem = datadict(dKey)
        
        crit = "EventTypeId = 1 AND ProductionLineCode = '" & sowItem.ProductionLine & "' AND ProductCode = '" & sowItem.ProductCode & "' AND EventDate = " & sowItem.SowingDate
        
        rs.FindFirst crit
        If rs.NoMatch Then
            'Insert New Record
            rs.AddNew
            rs!SowingDate = sowItem.SowingDate
            rs!EventDate = sowItem.SowingDate
            rs!EmployeeName = sowItem.SowerName
            rs!EventTypeId = sowItem.EventTypeId
            rs!ProductionLineCode = sowItem.ProductionLine
            rs!ProductCode = sowItem.ProductCode
            rs!UnitCode = sowItem.UnitCode
            rs!GreenHouseCode = sowItem.GreenHouseCode
            rs!Quantity = sowItem.ActualCellCount
            rs!SeedBatchNumber = sowItem.SeedBatchNo
            rs.Update
        End If
        
    Next
    
    If MsgBox("Save changes?", vbQuestion + vbYesNo) = vbYes Then
        ws.CommitTrans
    Else
        ws.Rollback
    End If
    
    rs.Close
    db.Close
    ws.Close
    
    Set db = Nothing
    Set rs = Nothing
    Set ws = Nothing
    Set sowItem = Nothing
    Set datadict = Nothing
    
    Exit Sub
    
errHandler:
    ws.Rollback
    rs.Close
    db.Close
    ws.Close
    
    Set db = Nothing
    Set rs = Nothing
    Set ws = Nothing
    Set sowItem = Nothing
    Set datadict = Nothing
    
    Call Common.FatalError(Err.Description)
    
End Sub

Solution

When you link an Access table to a SharePoint list, you're essentially creating a live connection. Changes made to the linked table are immediately reflected in the SharePoint list, bypassing any local transaction management.

Reference: https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/workspace-begintrans-method-dao#remarks

To summarize the reason why Transactions Don't Work:

Updates to linked tables are typically synchronized with the SharePoint list in real-time, Access doesn't maintain a local copy of the data for transaction purposes. While SharePoint likely handles transactions at its own level, which is beyond the control of your Access application.

Potential Solutions:

1. Import/Export Approach:

Instead of linking the table, import the data from SharePoint into a local Access table.
Perform transactions on the local table.
Export the modified data back to SharePoint after a successful transaction. This approach provides more control over data manipulation but may require additional steps.

2. Error Handling and Retry Logic: Implement robust and careful error handlers in the database code at each change made, to detect issues during data insertion, and retry or rollback these changes.

For completeness, I read about other techniques including the use of APIs, and third party tools, however these 2 solutions are my favorite, espicially second one.