基于Excel参考值删除Access表记录时遇运行时错误求助

• 核心错误原因：你的SQL语句中，字符串类型的UniqRef值未用单引号包裹。直接拼接UniqRef到SQL后，数据库会把AY100531MA2703AG识别为字段名而非字符串值，导致系统判定缺少必需参数。

• 修复后的代码：

Dim oConn As Object
Dim oRs As Object
Dim sConn As String, strSQL As String, UniqRef As String
Dim StrDBPath As String

UniqRef = Worksheets("BL, MO or HC").Range("L9").Value

StrDBPath = "\\rbsres01\shareddata\ctcbir\Everyone\Business Unit Admin – AD1000\MI Folder\Businessline Quality SharePoint\2023-03-20CallQualityDatabaseHR.accdb"
sConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
        "Data Source=" & StrDBPath & ";" & _
        "Jet OLEDB:Engine Type=5;" & _
        "Persist Security Info=False;"

Set oConn = CreateObject("ADODB.Connection")
Set oRs = CreateObject("ADODB.Recordset")

'Open Connection
oConn.Open sConn

'Define SQL String - 给字符串值添加单引号，单个值用=比IN更高效
strSQL = "DELETE * FROM ObservationTable WHERE [UniqRef] = '" & UniqRef & "';"

'Execute Query
oConn.Execute strSQL

'Close Connection
oConn.Close

'Destroy Objects
Set oConn = Nothing
Set oRs = Nothing

• 额外优化说明：
  • 修正变量声明：VB中Dim sConn, strSQL, UniqRef As String仅UniqRef为String类型，另外两个变量默认是Variant，需明确每个变量的类型。
  • 单个值匹配场景下，用=替代IN更简洁高效；若后续需处理多个UniqRef值，要确保每个值都包裹单引号，格式示例："('Val1','Val2')"。
  • 补充StrDBPath的类型声明，避免隐式Variant类型引发的潜在问题。

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