Access报错：SQL Server后端“This Recordset is not Updatable”问题求助

核心原因 #

添加的[5-10_TeamActiveStatus_tbl]、[2-01_TeamIDNormalized_tbl]、[5-10_TeamActiveStatus_Codes_tbl]导致查询触发了Access不可更新查询的规则，常见原因包括：

• 多表连接后出现一对多关系，Access无法确定要更新的唯一记录（比如一个客户对应多条团队状态记录）
• INNER JOIN过滤后，查询结果无法直接映射回原表的唯一行
• 连接字段未设置主键或唯一索引，Access无法追踪更新源

具体解决方案 #

1. 分离组合框数据源与表单记录源 #

表单的记录源保留原可更新查询的结构（仅包含[1-01_Clients_tbl]、[1-01-Clients_TransferXtra_tbl]、[_Circular_Temp]），确保核心数据可编辑。
组合框的RowSource单独设置为对应表的查询，比如针对[5-10_TeamActiveStatus_Codes_tbl]的独立查询，既满足组合框的数据需求，又不影响表单的可更新性。

2. 用子查询替代直接表连接 #

如果需要在表单中显示新增表的字段，改用子查询在SELECT语句中获取值，避免多表连接破坏查询的可更新性。修改后的SQL示例：

SELECT 
    [1-01_Clients_tbl].CNR, 
    [FNC] & " " & [SNC] AS [Service User Full Name], 
    [1-01_Clients_tbl].PT AS [Physiotherapist name (adjust)], 
    [1-01_Clients_tbl].[PDS Score (txt)] AS [Score (txt)], 
    [1-01_Clients_tbl].[PDS Score (nmbr)] AS [Score (No)], 
    [1-01_Clients_tbl].[Date for WLI], 
    [1-01_Clients_tbl].DOB AS [Date of Birth], 
    [1-01_Clients_tbl].TASCode, 
    [1-01_Clients_tbl].PTact AS [Active Status Physio Details (adjust)], 
    [_Circular_Temp].ActiveStatus AS [Active Status APH],
    -- 子查询获取团队状态文本
    (SELECT TextVisible 
     FROM [5-10_TeamActiveStatus_Codes_tbl] 
     INNER JOIN [5-10_TeamActiveStatus_tbl] 
         ON [5-10_TeamActiveStatus_Codes_tbl].TAScodeID = [5-10_TeamActiveStatus_tbl].TeamActiveStatusCode 
     WHERE [5-10_TeamActiveStatus_tbl].CNR = [1-01_Clients_tbl].CNR) AS TextVisible,
    -- 子查询获取团队ID
    (SELECT CTeamID 
     FROM [2-01_TeamIDNormalized_tbl] 
     WHERE CNR = [1-01_Clients_tbl].CNR) AS CTeamID
FROM ([1-01_Clients_tbl] 
LEFT JOIN [1-01-Clients_TransferXtra_tbl] ON [1-01_Clients_tbl].CNR = [1-01-Clients_TransferXtra_tbl].CNR) 
LEFT JOIN _Circular_Temp ON [1-01_Clients_tbl].CNR = [_Circular_Temp].CNR
WHERE ((([1-01_Clients_tbl].CNR)<>1 Or ([1-01_Clients_tbl].CNR)=2) 
AND (([_Circular_Temp].ActiveStatus)="yes"));

3. 优化表连接关系 #

• 将INNER JOIN [5-10_TeamActiveStatus_tbl]改为LEFT JOIN，确保所有客户记录都被保留，避免过滤导致的更新映射问题
• 给[5-10_TeamActiveStatus_tbl]的CNR字段设置唯一索引或主键，避免一个客户对应多条记录导致查询结果重复

4. 使用DLookup函数获取字段值 #

如果子查询性能不足，可改用DLookup函数直接获取字段值，示例：

SELECT 
    [1-01_Clients_tbl].CNR, 
    [FNC] & " " & [SNC] AS [Service User Full Name], 
    -- 其他原有字段...
    DLookup("TextVisible", "[5-10_TeamActiveStatus_Codes_tbl]", "TAScodeID = (SELECT TeamActiveStatusCode FROM [5-10_TeamActiveStatus_tbl] WHERE CNR = '" & [1-01_Clients_tbl].CNR & "')") AS TextVisible,
    DLookup("CTeamID", "[2-01_TeamIDNormalized_tbl]", "CNR = '" & [1-01_Clients_tbl].CNR & "'") AS CTeamID
-- 原有FROM和WHERE语句...

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