如何通过Access VBA语句向SQL Server传入NULL值？

直接修复现有代码（处理NULL与日期格式） #

报错的核心原因是当[Expiration Date]为NULL时，直接拼接会导致SQL语句中出现连续逗号（比如, ,），同时日期类型值未用Access SQL要求的#分隔符包裹。

修改步骤： #

1. 单独处理dateExpirationDate的NULL情况：
   • 若值为NULL，在SQL中写入NULL而非空字符串；
   • 非NULL时，用#包裹标准化格式的日期字符串，避免区域设置冲突。
2. 同步处理其他可能为NULL的数值字段（如rs!LevelS、rs!FAI），防止同类语法错误。

修改后的代码片段：

Dim dateExpirationDate As Variant
dateExpirationDate = rs![Expiration Date]

' 处理Expiration Date的NULL和格式问题
Dim strExpirationDate As String
If IsNull(dateExpirationDate) Then
    strExpirationDate = "NULL"
Else
    strExpirationDate = "#" & Format(dateExpirationDate, "yyyy-mm-dd hh:nn:ss") & "#"
End If

' 重构INSERT语句
strSQL = "INSERT INTO [PO Receipts] ([Lot Number], [Date Received], [Part Number], [Rev], [Purchase Order], POLine, [Vendor Code], " & _
          "[Total Received], [Sample ID Only], [Sample Level S], [Sample Level I], [Sample Level II], [Sample Level III], " & _
          "[Accept Quantity], [Reject Quantity], Inspector, Remarks, [Type], PackList, FAI, [Expiration Date], [Heat Code]) VALUES (" & _
          "'" & strLot & "', #" & Format([Receive Date], "yyyy-mm-dd hh:nn:ss") & "#, '" & rs!Part & "', '" & rs!Rev & "', '" & rs!PurchaseOrder & "', '" & rs!Line & "', " & _
          "'" & [Vendor Code] & "', " & rs!Received & ", " & Format(CInt(rs!IDOnly)) & ", " & IIf(IsNull(rs!LevelS), "NULL", rs!LevelS) & ", " & _
          IIf(IsNull(rs!LevelI), "NULL", rs!LevelI) & ", " & IIf(IsNull(rs!LevelII), "NULL", rs!LevelII) & ", " & IIf(IsNull(rs!LevelIII), "NULL", rs!LevelIII) & ", " & _
          IIf(IsNull(rs!Accepted), "NULL", rs!Accepted) & ", " & IIf(IsNull(rs!Rejected), "NULL", rs!Rejected) & ", '" & [Inspector] & "', '" & rs!Remarks & "', '1', '" & [cboPL] & "', " & _
          IIf(IsNull(rs!FAI), "NULL", rs!FAI) & ", " & strExpirationDate & ", '" & rs![Heat Code] & "')"
con.Execute strSQL, , adCmdText + adExecuteNoRecords

更优方案：使用参数化查询（推荐） #

拼接SQL字符串易引发语法错误、SQL注入风险，参数化查询能彻底解决这些问题，同时代码更易维护。

示例代码：

Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
cmd.ActiveConnection = con
cmd.CommandType = adCmdText

' 定义带参数占位符的INSERT语句
cmd.CommandText = "INSERT INTO [PO Receipts] ([Lot Number], [Date Received], [Part Number], [Rev], [Purchase Order], POLine, [Vendor Code], " & _
                  "[Total Received], [Sample ID Only], [Sample Level S], [Sample Level I], [Sample Level II], [Sample Level III], " & _
                  "[Accept Quantity], [Reject Quantity], Inspector, Remarks, [Type], PackList, FAI, [Expiration Date], [Heat Code]) " & _
                  "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

' 按顺序添加参数（自动处理NULL、日期格式和特殊字符）
cmd.Parameters.Append cmd.CreateParameter("LotNumber", adVarChar, adParamInput, 50, strLot)
cmd.Parameters.Append cmd.CreateParameter("DateReceived", adDate, adParamInput, , [Receive Date])
cmd.Parameters.Append cmd.CreateParameter("PartNumber", adVarChar, adParamInput, 50, rs!Part)
cmd.Parameters.Append cmd.CreateParameter("Rev", adVarChar, adParamInput, 10, rs!Rev)
cmd.Parameters.Append cmd.CreateParameter("PurchaseOrder", adVarChar, adParamInput, 20, rs!PurchaseOrder)
cmd.Parameters.Append cmd.CreateParameter("POLine", adVarChar, adParamInput, 10, rs!Line)
cmd.Parameters.Append cmd.CreateParameter("VendorCode", adVarChar, adParamInput, 20, [Vendor Code])
cmd.Parameters.Append cmd.CreateParameter("TotalReceived", adInteger, adParamInput, , rs!Received)
cmd.Parameters.Append cmd.CreateParameter("SampleIDOnly", adInteger, adParamInput, , CInt(rs!IDOnly))
cmd.Parameters.Append cmd.CreateParameter("LevelS", adDouble, adParamInput, , rs!LevelS)
cmd.Parameters.Append cmd.CreateParameter("LevelI", adDouble, adParamInput, , rs!LevelI)
cmd.Parameters.Append cmd.CreateParameter("LevelII", adDouble, adParamInput, , rs!LevelII)
cmd.Parameters.Append cmd.CreateParameter("LevelIII", adDouble, adParamInput, , rs!LevelIII)
cmd.Parameters.Append cmd.CreateParameter("AcceptQuantity", adInteger, adParamInput, , rs!Accepted)
cmd.Parameters.Append cmd.CreateParameter("RejectQuantity", adInteger, adParamInput, , rs!Rejected)
cmd.Parameters.Append cmd.CreateParameter("Inspector", adVarChar, adParamInput, 50, [Inspector])
cmd.Parameters.Append cmd.CreateParameter("Remarks", adVarChar, adParamInput, 255, rs!Remarks)
cmd.Parameters.Append cmd.CreateParameter("Type", adVarChar, adParamInput, 10, "1")
cmd.Parameters.Append cmd.CreateParameter("PackList", adVarChar, adParamInput, 50, [cboPL])
cmd.Parameters.Append cmd.CreateParameter("FAI", adInteger, adParamInput, , rs!FAI)
cmd.Parameters.Append cmd.CreateParameter("ExpirationDate", adDate, adParamInput, , rs![Expiration Date])
cmd.Parameters.Append cmd.CreateParameter("HeatCode", adVarChar, adParamInput, 50, rs![Heat Code])

' 执行命令
cmd.Execute , , adExecuteNoRecords

Set cmd = Nothing

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