VBA SQL更新SharePoint列表：能否用单个ADO连接操作多个列表？

现状与问题 #

目前已通过VBA/ADO实现SharePoint列表的更新操作，但当前代码需要关闭原有列表连接、重新打开新列表连接才能操作不同列表。此前操作Access数据库时，可通过单个连接+SQL指定表名跨表操作，但该方式用于SharePoint列表时会报错：No value given for one or more required parameters。现需确认是否能通过同一站点连接，直接用SQL指定列表完成多列表更新。

当前运行正常的代码：

Public Sub Update_some_list()
    Dim ListID As String, SharePointSite As String
    ListID = "Test_Staff"
    SharePointSite = "https://companyname.sharepoint.com/sites/proc2126"
    Dim c As New ADODB.Connection
    c.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;DATABASE=" & SharePointSite & ";LIST=" & ListID
    Dim SQL As String
    SQL = "UPDATE [Test_Staff] SET [StaffName]='ChrisTest' WHERE [UserName]='melvilc';"
    c.Execute SQL
    c.Close ' 希望去掉关闭重连的步骤
    ListID = "Duplicate_Test"
    c.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;DATABASE=" & SharePointSite & ";LIST=" & ListID
    SQL = "UPDATE [Duplicate_Test] SET [Three]='ChrisTest' WHERE [Four]='Deleted';"
    c.Execute SQL
End Sub

方案说明 #

1. ACE OLEDB WSS提供者的限制

使用Microsoft.ACE.OLEDB.12.0的WSS提供者时，连接字符串中的LIST参数会绑定到单个SharePoint列表，单个连接仅能操作该指定列表，无法通过SQL直接跨列表操作。因此必须关闭当前连接、重新指定LIST参数打开新连接，才能操作其他列表。

可以通过封装函数简化重复的连接逻辑，减少代码冗余：

' 封装列表更新操作
Private Sub ExecuteListUpdate(siteUrl As String, listName As String, sqlStmt As String)
    Dim conn As New ADODB.Connection
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;WSS;DATABASE=" & siteUrl & ";LIST=" & listName
    conn.Execute sqlStmt
    conn.Close
    Set conn = Nothing
End Sub

' 主调用过程
Public Sub Update_some_list()
    Dim SharePointSite As String
    SharePointSite = "https://companyname.sharepoint.com/sites/proc2126"
    
    ' 更新Test_Staff列表
    ExecuteListUpdate SharePointSite, "Test_Staff", _
        "UPDATE [Test_Staff] SET [StaffName]='ChrisTest' WHERE [UserName]='melvilc';"
    
    ' 更新Duplicate_Test列表
    ExecuteListUpdate SharePointSite, "Duplicate_Test", _
        "UPDATE [Duplicate_Test] SET [Three]='ChrisTest' WHERE [Four]='Deleted';"
End Sub

2. 跨列表操作的优化方案（推荐）

若想避免频繁开关连接，可使用SharePoint REST API或CSOM（客户端对象模型），这两种方式支持在单个会话内操作多个列表，更贴合SharePoint的现代操作逻辑。

以下是REST API的示例实现（需引入VBA-JSON库解析响应）：

' 更新指定列表项字段
Private Sub UpdateListViaREST(siteUrl As String, listName As String, itemId As Integer, fieldName As String, fieldValue As String)
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    
    ' 获取列表实体类型名称
    Dim entityType As String
    entityType = GetListItemEntityType(siteUrl, listName)
    
    ' 构建REST请求地址
    Dim restUrl As String
    restUrl = siteUrl & "/_api/web/lists/getbytitle('" & listName & "')/items(" & itemId & ")"
    
    ' 设置请求头
    xmlHttp.Open "POST", restUrl, False
    xmlHttp.setRequestHeader "Accept", "application/json;odata=verbose"
    xmlHttp.setRequestHeader "Content-Type", "application/json;odata=verbose"
    xmlHttp.setRequestHeader "X-HTTP-Method", "MERGE"
    xmlHttp.setRequestHeader "If-Match", "*"
    
    ' 构建请求体
    Dim jsonBody As String
    jsonBody = "{'__metadata':{'type':'" & entityType & "'},'" & fieldName & "':'" & fieldValue & "'}"
    xmlHttp.send jsonBody
    
    Set xmlHttp = Nothing
End Sub

' 获取列表项的实体类型名称
Private Function GetListItemEntityType(siteUrl As String, listName As String) As String
    Dim xmlHttp As Object
    Set xmlHttp = CreateObject("MSXML2.XMLHTTP.6.0")
    
    Dim restUrl As String
    restUrl = siteUrl & "/_api/web/lists/getbytitle('" & listName & "')?$select=ListItemEntityTypeFullName"
    
    xmlHttp.Open "GET", restUrl, False
    xmlHttp.setRequestHeader "Accept", "application/json;odata=verbose"
    xmlHttp.send
    
    ' 解析JSON响应（需引用VBA-JSON库）
    Dim responseJson As Object
    Set responseJson = JsonConverter.ParseJson(xmlHttp.responseText)
    GetListItemEntityType = responseJson("d")("ListItemEntityTypeFullName")
    
    Set xmlHttp = Nothing
    Set responseJson = Nothing
End Sub

总结 #

• 若继续使用ACE OLEDB WSS提供者，必须通过关闭重连切换列表，但可通过封装函数简化代码。
• 若要实现单个会话内操作多列表，优先选择SharePoint REST API或CSOM，既避免频繁连接操作，也更符合SharePoint的官方推荐方式。

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