使用Excel VBA通过Like方法从MS Access取数遇问题求助

问题原因 #

你的VBA代码中，SQL查询的Like条件未添加通配符，导致仅会匹配完全等于单元格内容的记录。输入LOT时，只会查找film_id = 'LOT'的记录，而数据库中实际是LOT/000，自然无法匹配。Access的Jet SQL语法中，Like需要用*作为通配符来匹配任意长度的后缀内容。

修复方案 #

方案1：直接添加通配符并处理特殊字符

修改VBA中的查询语句，在单元格内容后拼接*，同时替换内容中的单引号（避免SQL语法错误）：

Sub getdata()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim DbPath As String
    Dim shI As Worksheet, Sort As Worksheet
    Dim Query As String, searchValue As String

    Set shI = ThisWorkbook.Worksheets("Data")
    Set Sort = ThisWorkbook.Worksheets("Report")
    DbPath = "C:\mydatabase.accdb"

    ' 获取搜索值并处理单引号
    searchValue = Replace(Sort.Cells(2, 2).Value, "'", "''")

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = DbPath
        .Open
    End With

    ' 添加通配符*
    Query = "Select * FROM Film_build WHERE film_id like '" & searchValue & "*'"

    Set rs = New ADODB.Recordset
    rs.Open Query, cn, adOpenDynamic, adLockBatchOptimistic

    shI.Range("A2").CopyFromRecordset rs
    
    ' 关闭资源
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cn = Nothing
End Sub

方案2：使用参数化查询（更安全，推荐）

参数化查询可以避免SQL注入风险，同时无需手动处理特殊字符：

Sub getdata()
    Dim cn As ADODB.Connection
    Dim cmd As ADODB.Command
    Dim rs As ADODB.Recordset
    Dim DbPath As String
    Dim shI As Worksheet, Sort As Worksheet
    Dim searchValue As String

    Set shI = ThisWorkbook.Worksheets("Data")
    Set Sort = ThisWorkbook.Worksheets("Report")
    DbPath = "C:\mydatabase.accdb"

    searchValue = Sort.Cells(2, 2).Value & "*" ' 添加通配符

    Set cn = New ADODB.Connection
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = DbPath
        .Open
    End With

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "Select * FROM Film_build WHERE film_id like ?"
        .Parameters.Append .CreateParameter("searchVal", adVarChar, adParamInput, 255, searchValue)
    End With

    Set rs = cmd.Execute
    shI.Range("A2").CopyFromRecordset rs
    
    ' 关闭资源
    rs.Close
    cn.Close
    Set rs = Nothing
    Set cmd = Nothing
    Set cn = Nothing
End Sub

验证说明 #

修改后，当单元格输入LOT时，SQL查询会变为WHERE film_id like 'LOT*'，和你在Access中手动运行的查询一致，就能匹配到LOT/000这类以LOT开头的记录。

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