从Access查询向SQL Server追加数据时出现异常错误

问题现象 #

• 从Access查询qryContact直接追加数据到SQL Server 2019的tmakContact表时，频繁弹出“记录已删除”提示框（无错误编号），排除字段类型不匹配问题（SQL Server列与查询字段定义完全一致，如三个nvarchar(255)字段，部分允许NULL但追加时仍报错）。
• 移除查询中来自外部SQL Server链接表的Relationship和PhoneNumType字段后，追加可正常执行；将查询结果生成本地临时表tmptblContact后再追加，也无异常。

差异原因分析 #

1. 跨服务器链接表的NULL值驱动转换问题
   Access通过ODBC链接多个SQL Server时，LEFT JOIN产生的NULL值在驱动层可能被误识别为“已删除记录”的标识。临时表作为本地Access表，会先将查询结果转换为Access兼容的存储格式，再同步到SQL Server，绕过了驱动直接处理跨服务器NULL值的逻辑。

2. 查询计算字段的隐式类型冲突
   查询中Trim([dbo_ContactRelationship]![Descr])、地址拼接等计算字段，当源字段为NULL时，Access与ODBC驱动对空值的处理逻辑不一致，导致传输到SQL Server时触发异常。

3. ODBC链接配置缺失兼容参数
   自定义ODBC字符串未启用ANSI兼容设置，导致跨服务器查询的结果集在Access中被错误标记为包含已删除记录。

稳定追加的解决方法 #

方法1：显式处理NULL值 #

在qryContact的SELECT语句中，用Nz函数将可能为NULL的字段转换为空字符串或对应默认值，避免驱动层的空值误判：

SELECT Now() AS DataAsOf, 
       dbo_Contact.Id AS ContactId, 
       dbo_UserInfo.FullName, 
       dbo_Loaninfo.LoanNum, 
       dbo_ContactLoanLink.LoanId, 
       dbo_Contact.Name, 
       dbo_Contact.JobTitle, 
       Nz(dbo_Email.Addr, "") AS Email, 
       Nz(Trim([dbo_ContactRelationship]![Descr]), "") AS Relationship, 
       Nz(dbo_Contact.Company, "") AS Company, 
       Nz([Add1], "") & " " & Nz([Add2], "") AS Address, 
       StrConv(Nz([City], ""), 3) & ", " & Nz([StateCode], "") & " " & Nz([Zip], "") AS CityStateZip, 
       Nz(qryPhone.Number, "") AS Number, 
       Nz(qryPhone.PhoneNumType, "") AS PhoneNumType
FROM ((((dbo_Loaninfo INNER JOIN ((dbo_ContactLoanLink INNER JOIN dbo_Contact ON dbo_ContactLoanLink.ContactId = dbo_Contact.Id) LEFT JOIN dbo_Address ON dbo_Contact.BusAddrAId = dbo_Address.Id) ON dbo_Loaninfo.Id = dbo_ContactLoanLink.LoanId) LEFT JOIN dbo_ContactRelationship ON dbo_ContactLoanLink.ContactRelationshipId = dbo_ContactRelationship.Id) LEFT JOIN dbo_Email ON dbo_Contact.Id = dbo_Email.ContactId) LEFT JOIN qryPhone ON dbo_Contact.Id = qryPhone.ContactId) LEFT JOIN dbo_UserInfo ON dbo_Loaninfo.AssignedUserId = dbo_UserInfo.Id
WHERE (((dbo_Contact.InactiveFlag)="N") AND ((dbo_Loaninfo.LoanStatusId)<>1105) AND ((dbo_Loaninfo.InactiveFlag)="N") AND ((dbo_Loaninfo.PaidOffFlag)="N"));

方法2：将跨服务器关联逻辑移至SQL Server端 #

如果dbo_ContactRelationship和qryPhone来自另一SQL Server，在目标SQL Server创建跨服务器视图，把关联逻辑放在SQL Server内部处理，再将该视图链接到Access。这样Access查询的是单一链接对象，减少驱动层的处理复杂度。

方法3：调整ODBC链接字符串参数 #

在Linked Table Manager的自定义ODBC字符串中添加ANSI兼容参数：

DRIVER={ODBC Driver 17 for SQL Server};SERVER=你的服务器地址;DATABASE=你的数据库名;UID=用户名;PWD=密码;AnsiNPW=Yes;QuotedId=Yes

启用AnsiNPW=Yes确保NULL值和字符串处理符合ANSI标准，避免驱动层的异常判断。

方法4：自动化临时表中转流程 #

用VBA代码自动完成“查询转临时表→追加→删除临时表”的流程，保持与手动操作一致的稳定性：

Sub AppendContactData()
    On Error GoTo ErrorHandler
    
    ' 创建临时表
    DoCmd.RunSQL "SELECT * INTO tmptblContact FROM qryContact"
    
    ' 追加数据到SQL Server表
    DoCmd.RunSQL "INSERT INTO tmakContact (DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType) " & _
                 "SELECT DataAsOf, ContactId, FullName, LoanNum, LoanId, Name, JobTitle, Email, Relationship, Company, Address, CityStateZip, [Number], PhoneNumType FROM tmptblContact"
    
    ' 删除临时表
    DoCmd.RunSQL "DROP TABLE tmptblContact"
    
    Exit Sub
    
ErrorHandler:
    MsgBox "执行出错：" & Err.Description, vbCritical
    ' 清理临时表（如果存在）
    On Error Resume Next
    DoCmd.RunSQL "DROP TABLE tmptblContact"
End Sub

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