基于Excel用户输入修改Access查询SQL的VBA宏问题排查

你的代码主要有几个语法问题导致SQL执行失败，我帮你修正并扩展到支持单/双输入的场景：

问题分析 #

1. SQL拼接缺少必要空格：比如FROM和WHERE关键字前没有空格，会和前面的字段名连在一起造成语法错误；另外你在FROM语句后加了分号，这会直接结束SQL语句，后续的WHERE条件会被忽略或触发报错。
2. 字符串字段未加引号：从你的目标SQL能看出Dock_Rec_Problems_DGID是字符串类型，直接拼接单元格值会导致SQL无法识别字符串常量，引发语法错误。

修正后的单输入代码 #

Private Sub CommandButton4_Click()
    Const DbLoc As String = "MYfilepath"
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim wb1 As Workbook, ws1 As Worksheet, ws2 As Worksheet
    Dim SQL As String, userInput As String
    
    Set wb1 = Workbooks("mytool.xlsm")
    Set ws1 = wb1.Sheets("Inputs")
    Set ws2 = wb1.Sheets("raw")
    userInput = ws1.Range("D6").Value ' 获取单元格输入值
    
    ' 正确拼接SQL：注意关键字前的空格，字符串字段添加单引号，同时处理输入中的单引号避免报错
    SQL = "SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, " & _
          "Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, Dock_Rec_Problems.Vendor_Name, " & _
          "Dock_Rec_Problems.PO_Number, Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, " & _
          "Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, Dock_Rec_Problems.Num_Of_Cases, " & _
          "Dock_Rec_Problems.Dock_Rec_Problems_DGID " & _
          "FROM Dock_Rec_Problems " & _
          "WHERE [Dock_Rec_Problems_DGID] = '" & Replace(userInput, "'", "''") & "'"
    
    Set db = OpenDatabase(DbLoc)
    On Error Resume Next ' 捕获SQL执行过程中的错误
    Set rs = db.OpenRecordset(SQL, dbOpenSnapshot)
    On Error GoTo 0
    
    If rs Is Nothing Or rs.RecordCount = 0 Then
        MsgBox "Not found in database", vbInformation + vbOKOnly, "No Data"
        GoTo SubExit
    End If
    
    ' 可选：清空目标工作表旧数据，避免重复写入
    ws2.Cells.Clear
    ws2.Range("A1").CopyFromRecordset rs
    
SubExit:
    On Error Resume Next
    Application.Cursor = xlDefault
    If Not rs Is Nothing Then rs.Close
    ' 手动释放所有对象，避免内存泄漏
    Set rs = Nothing
    Set db = Nothing
    Set ws2 = Nothing
    Set ws1 = Nothing
    Set wb1 = Nothing
    Exit Sub
End Sub

扩展支持双输入的情况 #

如果需要支持两个输入（比如D6和D7单元格），可以修改WHERE条件为OR逻辑：

' 获取两个输入值
Dim userInput1 As String, userInput2 As String
userInput1 = ws1.Range("D6").Value
userInput2 = ws1.Range("D7").Value

' 拼接OR条件的SQL
SQL = "SELECT Dock_Rec_Problems.Merch_Name, Dock_Rec_Problems.Vendor_Error_Code, " & _
      "Dock_Rec_Problems.DC, Dock_Rec_Problems.Vendor_ID_IP, Dock_Rec_Problems.Vendor_Name, " & _
      "Dock_Rec_Problems.PO_Number, Dock_Rec_Problems.SKU_No, Dock_Rec_Problems.Item_Description, " & _
      "Dock_Rec_Problems.Casepack, Dock_Rec_Problems.Retail, Dock_Rec_Problems.Num_Of_Cases, " & _
      "Dock_Rec_Problems.Dock_Rec_Problems_DGID " & _
      "FROM Dock_Rec_Problems " & _
      "WHERE [Dock_Rec_Problems_DGID] = '" & Replace(userInput1, "'", "''") & "' " & _
      "OR [Dock_Rec_Problems_DGID] = '" & Replace(userInput2, "'", "''") & "'"

关键注意事项 #

• 空格规范：SQL关键字（FROM、WHERE、OR等）前面必须加空格，否则会和前面的内容连在一起破坏SQL结构。
• 字符串转义：使用Replace(userInput, "'", "''")替换输入中的单引号，避免SQL注入风险，同时防止输入含单引号时导致SQL语句中断。
• 错误处理：增加On Error语句捕获SQL执行错误，避免程序意外崩溃。
• 对象清理：退出子程序前手动释放所有对象，减少内存占用。

内容的提问来源于stack exchange，提问作者The Dude MAN