MS Access更新SharePoint链接表报错：Updating Linked Table not Supported in this ISAM

每日导出一份CSV文件，在MS Access中创建查询读取该CSV数据，更新SharePoint列表「sp_list」中对应cod_id的行（仅更新列值有变更的行），所用SQL代码如下：

UPDATE sp_list
    INNER JOIN csv_file
        ON sp_list.[cod_id] = csv_file.[cod_id] & ''
    SET sp_list.manager = csv_file.[manager],
        sp_list.vendor_code = csv_file.[vendor_code],
        sp_list.vendor_name = csv_file.[vendor_name]
    WHERE
        csv_file.[manager] = 'Test' 
        AND (
           (sp_list.manager & '')  <> (csv_file.[manager] & '') OR 
           (sp_list.vendor_code & '')  <> (csv_file.[vendor_code] & '') OR 
           (sp_list.vendor_name & '')  <> (csv_file.[vendor_name] & '') 
        );

在MS Access预览模式下无报错且能显示待更新行，但执行查询时出现报错「Updating Linked Table not Supported in this ISAM」，查询无法执行。

该报错原因是Access的ISAM驱动不支持直接通过JOIN外部数据源（如CSV）的方式更新链接的SharePoint列表，即使预览能识别待更新行，执行时也会触发限制。以下是三种可行的解决方式：

方法1：将CSV导入Access本地表后更新 #

• 先把每日导出的CSV文件导入Access本地表（例如命名为local_csv_data），可手动导入或通过宏/代码实现自动导入
• 修改UPDATE查询，基于本地表更新SharePoint链接表：

UPDATE sp_list
    INNER JOIN local_csv_data
        ON sp_list.[cod_id] = local_csv_data.[cod_id] & ''
    SET sp_list.manager = local_csv_data.[manager],
        sp_list.vendor_code = local_csv_data.[vendor_code],
        sp_list.vendor_name = local_csv_data.[vendor_name]
    WHERE
        local_csv_data.[manager] = 'Test' 
        AND (
           (sp_list.manager & '')  <> (local_csv_data.[manager] & '') OR 
           (sp_list.vendor_code & '')  <> (local_csv_data.[vendor_code] & '') OR 
           (sp_list.vendor_name & '')  <> (local_csv_data.[vendor_name] & '') 
        );

方法2：用VBA代码逐行验证更新 #

通过VBA读取CSV数据，对比SharePoint列表对应行，仅更新有变更的记录，绕开ISAM限制：

Sub UpdateSPListFromCSV()
    Dim db As DAO.Database
    Dim rsCSV As DAO.Recordset
    Dim rsSP As DAO.Recordset
    Dim strCSVFolder As String
    Dim strCSVFileName As String
    
    ' 替换为实际CSV文件所在文件夹和文件名
    strCSVFolder = "C:\YourCSVFolder\"
    strCSVFileName = "daily_data.csv"
    
    Set db = CurrentDb()
    ' 打开CSV记录集
    Set rsCSV = db.OpenRecordset("SELECT * FROM [Text;FMT=Delimited;HDR=Yes;DATABASE=" & strCSVFolder & "].[" & strCSVFileName & "] WHERE manager='Test'")
    ' 打开可编辑的SharePoint列表记录集
    Set rsSP = db.OpenRecordset("SELECT cod_id, manager, vendor_code, vendor_name FROM sp_list", dbOpenDynaset)
    
    Do While Not rsCSV.EOF
        ' 查找对应cod_id的SharePoint记录
        rsSP.FindFirst "cod_id = '" & rsCSV!cod_id & "'"
        If Not rsSP.NoMatch Then
            ' 检查字段是否有变更（用Nz处理空值）
            If (Nz(rsSP!manager, "") <> Nz(rsCSV!manager, "")) Or _
               (Nz(rsSP!vendor_code, "") <> Nz(rsCSV!vendor_code, "")) Or _
               (Nz(rsSP!vendor_name, "") <> Nz(rsCSV!vendor_name, "")) Then
                ' 执行更新
                rsSP.Edit
                rsSP!manager = rsCSV!manager
                rsSP!vendor_code = rsCSV!vendor_code
                rsSP!vendor_name = rsCSV!vendor_name
                rsSP.Update
            End If
        End If
        rsCSV.MoveNext
    Loop
    
    ' 释放资源
    rsSP.Close
    rsCSV.Close
    Set rsSP = Nothing
    Set rsCSV = Nothing
    Set db = Nothing
    MsgBox "更新完成"
End Sub

注意：替换代码中的CSV路径和文件名，确保Access具备读取CSV和修改SharePoint列表的权限。

方法3：用Power Automate替代Access操作 #

若环境允许，直接使用Power Automate（原Flow）每日读取存储在OneDrive/SharePoint文档库的CSV文件，自动对比并更新目标SharePoint列表，完全绕开Access的ISAM限制，更适合长期自动化场景。

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