如何提升VBA(ADODB)更新SharePoint列表的处理速度？

我用VBA结合ADODB操作SharePoint列表时，遇到了严重的性能瓶颈：处理400条记录耗时约40秒。经过定位，代码中的rs.Fields.Item(Field_name).Value = "Update_value"语句是核心问题点——注释掉这行后，处理耗时骤降到1秒左右。

我尝试过以下优化手段，但均未改善速度：

• 修改CursorType（如设置为adOpenForwardOnly）
• 调整LockType（如设置为adLockPessimistic）
• 变更rs.Update的调用时机（改为批量更新）

之后参考建议改用SQL循环更新的方式，400条记录仍耗时约38秒，性能没有明显提升。

第一段代码（Recordset逐条更新） #

Sub Update_Sharepoint_Table()

    SPO_url = "https://..."
    Table_name = "SPO_List"
   
    Set cn = CreateObject("ADODB.Connection")

    cn.Open "Provider=Microsoft.ACE.OLEDB.16.0;WSS;IMEX=2;RetrieveIds=Yes;DATABASE=" _
                            & SPO_url & ";LIST=" & Table_name & ";"

                                               
    Set rs = CreateObject("ADODB.Recordset")
    With rs
        .Source = Table_name
        .ActiveConnection = cn
        .CursorType = adOpenKeyset
        .LockType = adLockOptimistic
        .Open
    End With

    Field_name = "Field_name"    
    rs.MoveFirst

    Do until rs.EOF
        sample = rs.Fields.Item(Field_name).Value
        If sample = "Something" Then
            rs.Fields.Item(Field_name).Value = "Update_value"  ' 注释掉这行后处理速度大幅提升
        End If
        
        rs.Update
        rs.MoveNext
    Loop

End Sub

第二段代码（SQL循环更新） #

Sub SPListUpdate()
    
    Const ServerUrl As String = "https://contoso.sharepoint.com/sites/ABC/"
    Const ListName As String = "{e5r6t7h8-3d0e-4890-8111-3531bde50f4k}" 'List GUID
    
    Dim Conn As New ADODB.Connection
    Dim Sql As String
    Dim recsUpdated As Long
    
    With Conn ' 打开连接
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
                             "DATABASE=" & ServerUrl & ";" & _
                             "LIST=" & ListName & ";"
        .Open
    End With

    For i=1 to 400
        Sql = "update [" & ListName & "] set [Target_col] = 'Blah' where [Title] = '" & i & "' "
        Conn.Execute Sql, recsUpdated
        Debug.Print recsUpdated & " record(s) updated"
    Next

End Sub

内容的提问来源于stack exchange，提问作者KK0414