How to improve the processing speed for updating Sharepoint List value from VBA (ADODB)

Question

Is there any method to improve the processing speed for updating Sharepoint List value from VBA(ADODB) ? I've identified the part of the code that makes slower the processing speed. It is [rs.Fields.Item(Field_name).Value = "Update_value"] in the following sample code. It takes approx. 40 sec to finish 400 record rows. (Too slow) But if I comment-out the above part, it takes approx. 1 sec to finish.

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"  ' If I comment-out this line, the processing speed is dramatically improved.
        End If
        
        rs.Update
        rs.MoveNext
    Loop

End Sub

I tried to change CursorType (adOpenForwardOnly,etc.), LockType (adLoockPessimistic,etc.), and the timing of rs.Update (Batch Update). But all of these don't work to improve the processing speed.

Receiving advise from Tim Williams, I tried the following code, but the result is approx. 38 sec to finish 400 items.

 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 ' Open the connection
        .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

Answer 1

Here's an example of running an update on a list in SharePoint:

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 ' Open the connection
        .ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;WSS;IMEX=0;RetrieveIds=Yes;" & _
                             "DATABASE=" & ServerUrl & ";" & _
                             "LIST=" & ListName & ";"
        .Open
    End With

    Sql = "update [" & ListName & "] set [Title] = 'Title3' where [Title] = 'Title2' "
    Conn.Execute Sql, recsUpdated
    
    Debug.Print recsUpdated & " record(s) updated"

End Sub

If you want to update multiple records at once you can use an "in" clause to send a list of values in one SQL call.

My test list has just "Title" as a field, with values of "Title_001", "Title_002", etc.

Const BATCH_COUNT As Long = 20
'...
Dim inList As String, sep As String, ttl As String, listCount As Long

'...
'...

    For i = 1 To 50
        listCount = listCount + 1
        ttl = "Title_" & Format(i, "000")
        inList = inList & sep & "'" & ttl & "'"
        sep = ","  'set after first record
        
        'execute an update? Reach the batch limit, or end of loop
        If listCount = BATCH_COUNT Or (i = 50 And listCount >0) Then
            Sql = " update [" & ListName & "] set [Title] = [Title] + '_updt' " & _
                  " where [Title] in (" & inList & ")"
            
            Debug.Print Sql
            Conn.Execute Sql, recsAffected
            Debug.Print recsAffected & " record(s) updated"
            inList = ""    'reset the list, separator, and count
            sep = ""
            listCount = 0
        End If
    Next
'...

SQL looks like this:

update [{af83a2e4-3d0e-4890-8111-3531bde50e2e}] 
set [Title] = [Title] + '_updt'  where [Title] in ('Title_001','Title_002','Title_003','Title_004','Title_005',
'Title_006','Title_007','Title_008','Title_009','Title_010',
'Title_011','Title_012','Title_013','Title_014','Title_015',
'Title_016','Title_017','Title_018','Title_019','Title_020')