MsAccess SQL更新表异常：TrafficDatabase更新失败求助

问题背景 #

我正在适配旧工具到新数据源，仅基于运单编号（SHIPPING_LOAD_NUMBER）更新记录的功能异常，其余功能正常。该字段已从NUMBER格式改为STRING格式，这是唯一变化点。

表单按钮触发两个独立宏：一个更新WCL_DATABASE表（正常工作），另一个更新TrafficDatabase表（更新失败并触发错误）。

更新WCL_DATABASE的SQL语句 #

UPDATE WCL_DATABASE_QUERY
SET WCL_DATABASE_QUERY.CARRIER = UCASE([Forms]![Edit_Load]![CARRIER])
    ,WCL_DATABASE_QUERY.COMMENTS = Forms!Edit_Load![COMMENTS]
    ,WCL_DATABASE_QUERY.DISPATCH_NOTES = Forms!Edit_Load![DISPATCH_NOTES]
    ,WCL_DATABASE_QUERY.CUSTOMER_CANCEL_DATE = [Forms]![Edit_Load]![CUSTOMER_CANCEL_DATE]
    ,WCL_DATABASE_QUERY.TRAFFIC_SPECIALIST = [Forms]![Edit_Load]![TRAFFIC_SPECIALIST]
    ,WCL_DATABASE_QUERY.RR_DATE = [Forms]![Edit_Load]![RR_DATE]
    ,WCL_DATABASE_QUERY.RA_DATE = [Forms]![Edit_Load]![RA_DATE]
    ,WCL_DATABASE_QUERY.PLANNED_SHIP_DATE = [Forms]![Edit_Load]![PLANNED_SHIP_DATE]
    ,WCL_DATABASE_QUERY.CONFIRMATION_NUMBER = [Forms]![Edit_Load]![CONFIRMATION_NUMBER]
    ,WCL_DATABASE_QUERY.WR_ID = [Forms]![Edit_Load]![WR_ID]
    ,WCL_DATABASE_QUERY.LAST_UPDATE = Now()
WHERE (
        ((WCL_DATABASE_QUERY!SHIPPING_LOAD_NUMBER) = Forms!Edit_Load!SHIPPING_LOAD_NUMBER_Title)
        AND ((WCL_DATABASE_QUERY.SHIPPING_LOAD_NUMBER) <> "0")
        );

更新TrafficDatabase的SQL语句 #

UPDATE TrafficDatabase_Query
SET TrafficDatabase_Query.CARRIER_ID = ucase([Forms]![Edit_Delivery]![CARRIER])
    ,TrafficDatabase_Query.COMMENTS = Forms!Edit_Delivery![COMMENTS]
    ,TrafficDatabase_Query.DISPATCH_NOTES = Forms!Edit_Delivery![DISPATCH_NOTES]
    ,TrafficDatabase_Query.CUSTOMER_CANCEL_DATE = [Forms]![Edit_Delivery]![CUSTOMER_CANCEL_DATE]
    ,TrafficDatabase_Query.TRAFFIC_SPECIALIST = [Forms]![Edit_Delivery]![TRAFFIC_SPECIALIST]
    ,TrafficDatabase_Query.RR_DATE = [Forms]![Edit_Delivery]![RR_DATE]
    ,TrafficDatabase_Query.RA_DATE = [Forms]![Edit_Delivery]![RA_DATE]
    ,TrafficDatabase_Query.PLANNED_SHIP_DATE = [Forms]![Edit_Delivery]![PLANNED_SHIP_DATE]
    ,TrafficDatabase_Query.CONFIRMATION_NUMBER = [Forms]![Edit_Delivery]![CONFIRMATION_NUMBER]
    ,TrafficDatabase_Query.WR_ID = [Forms]![Edit_Delivery]![WR_ID]
    ,TrafficDatabase_Query.LAST_UPDATE = NOW()
WHERE (
        (([TrafficDatabase_Query]!SHIPPING_LOAD_NUMBER) = Forms!Edit_Load!SHIPPING_LOAD_NUMBER_Title)
        AND (([TRAFFICDATABASE_Query]![SHIPPING_LOAD_NUMBER]) <> "0")
        );

排查与解决建议 #

• 统一字段引用大小写：TrafficDatabase的SQL中，字段引用混用了[TrafficDatabase_Query]!SHIPPING_LOAD_NUMBER和[TRAFFICDATABASE_Query]![SHIPPING_LOAD_NUMBER]，虽然Access不区分大小写，但统一写法可避免潜在解析异常，建议全部改为[TrafficDatabase_Query]!SHIPPING_LOAD_NUMBER。
• 显式转换匹配值类型：确保WHERE子句中两边的匹配值都是STRING类型，将表单控件值转换为字符串：

  WHERE (
      (([TrafficDatabase_Query]!SHIPPING_LOAD_NUMBER) = CStr(Forms!Edit_Load!SHIPPING_LOAD_NUMBER_Title))
      AND (([TrafficDatabase_Query]!SHIPPING_LOAD_NUMBER) <> "0")
      );
  

• 检查表单控件类型：确认Forms!Edit_Load!SHIPPING_LOAD_NUMBER_Title控件的输出类型为字符串。如果控件仍为数值类型，与STRING字段匹配时会因隐式转换失败（比如带前导零的运单编号无法匹配）。
• 验证查询基础表字段类型：确认TrafficDatabase_Query的基础表中SHIPPING_LOAD_NUMBER确实是STRING类型，没有在查询定义中被转换为数值。
• 单独测试WHERE子句：将TrafficDatabase的WHERE子句单独作为SELECT语句执行，查看是否能返回预期记录，以此判断是匹配条件问题还是UPDATE语句本身的问题。

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