Field cannot be updated - VBA SQL

I'm facing an issue when trying to update a Microsoft list. It goes well to SELECT and UPDATE some fields, but not to INSERT INTO.

Here is my code:

Sub SPListPushData()
    
    Const SiteUrl As String = "https://myperfectwebsite"
    Const ListName As String = "{123456789}"
    Dim Conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String
    
    Conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=2;RetrieveIds=Yes;" & "DATABASE=" & SiteUrl & ";" & "LIST=" & ListName & ";"
    Conn.Open
    
    sql = "INSERT INTO [" & ListName & "] ([MyOrder],[Comment]) VALUES ('12345','Test')"
    Set rs = New ADODB.Recordset
    rs.Open sql, Conn, adOpenDynamic
    
    rs.Close
    Conn.Close
    
End Sub

The error I get is :

Execution error -2147217887 (80040e21): field MyOrder is not a field which could be updated

What am I doing wrong, please?

EDIT : Is it because the connection string use Microsoft.ACE.OLEDB ? I saw that it's a Readonly method, but looks strange as my UPDATE method works

Solution

Here's an extension of my previous answer with added insert/update/delete steps.

The issue you're facing is connected to the IMEX setting you use when opening your connection, as explained here:
https://stackoverflow.com/a/28569981/478884

The MS article linked there is now a 404, but here's an archived verison: https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/194124

The possible settings of IMEX are:

 0 is Export mode
 1 is Import mode
 2 is Linked mode (full update capabilities)

'Use ADO to query a recordset from a SharePoint List
'Needs a VBA Project reference to "Microsoft ActiveX Data Objects x.x Library"
Sub SPListQuery()
    
    Const SiteUrl As String = "https://contoso.sharepoint.com/sites/siteA"
    
    Const listName As String = "TestList" 'List/Library Name or GUID
    
    Dim Conn As New ADODB.Connection, Conn2 As New ADODB.Connection
    Dim rs As ADODB.Recordset, sql As String, recsAffected As Long
    
    'open a couple of connections
    Set Conn = SPConnection(SiteUrl, listName, 2)  'Read, Change, Save Changes
    Set Conn2 = SPConnection(SiteUrl, listName, 0) 'Inserts
    
    '#### query all records for display
    sql = "SELECT * FROM [" & listName & "]"
    Set rs = New ADODB.Recordset
    rs.Open sql, Conn, adOpenStatic
    
    With ThisWorkbook.Worksheets("Results")
        .Cells.Clear 'clear any previous data
        RecordsetToWorksheet rs, .Range("A1")
    End With
    rs.Close
    
    '### insert a record (recordset method; works with connection IMEX=2)
    sql = "SELECT * FROM [" & listName & "] where false" 'opens an empty recordset
    rs.Open sql, Conn, adOpenKeyset, adLockOptimistic
    rs.AddNew
    rs.Fields("Title") = "My example title"
    'add any other fields here
    rs.Update
    rs.Close
    
    '### insert a record (SQL method; works with connection IMEX=0, 
    '       but does *not* work when IMEX=2)
    sql = "insert into [" & listName & "] ([Title]) values ('Special title')"
    
   'Conn.Execute sql, recsAffected   '<<<IMEX=2; fails with the same error you saw
    Conn2.Execute sql, recsAffected  'IMEX=0; works

    Debug.Print recsAffected & " record(s) inserted"
    
    '### update a record
    sql = "update [" & listName & "] t set t.Title = 'My new example title' " & _
           " where t.Title like 'My example title' "
    Conn.Execute sql, recsAffected
    Debug.Print recsAffected & " record(s) updated"
    
    '### delete some records
    sql = "delete FROM [" & listName & "] t where t.Title like '%updt%' "
    Conn.Execute sql, recsAffected
    Debug.Print recsAffected & " record(s) deleted"
    
    Conn.Close  'close the connections
    Conn2.Close
End Sub
    
'get a sharepoint connection
Function SPConnection(SiteUrl As String, listName As String, imex As Long) As ADODB.Connection
    Set SPConnection = New ADODB.Connection
    SPConnection.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;" & _
                                    "IMEX=" & imex & ";RetrieveIds=Yes;" & _
                                    "DATABASE=" & SiteUrl & ";" & _
                                    "LIST=" & listName & ";"
    SPConnection.Open
End Function


'Copy data from a recordset `rs` to a worksheet, starting at `StartCell`
Sub RecordsetToWorksheet(rs As ADODB.Recordset, StartCell As Range)
    Dim f As ADODB.Field, i As Long
    For Each f In rs.Fields
            'Debug.Print f.Name, f.Type
            StartCell.Offset(0, i).Value = f.Name
            i = i + 1
        Next f
    If Not rs.EOF Then StartCell.Offset(1).CopyFromRecordset rs
End Sub