SQL 查询在 Excel 中不起作用，但在 Access 中起作用

Question

对于下面列出的代码，除了第一个 SQL 查询外，它运行良好。我正在从工作簿中提取地址和状态信息，并对信息运行查询以查找地址在表中出现的次数。如果我在查询发送到 Access 之前运行代码并停止它，我可以从立即窗口中提取查询命令，转到 Access，然后运行查询没有问题。但是，如果我只是运行 VBA 程序并让它将查询发送到 Access，我会一直得到 0 的结果。长话短说，查询将在 Access 中运行并提供正确的结果，但是当 Excel VBA 将查询发送到 Access 时，我的结果一直为零(并且没有错误消息)。任何帮助将不胜感激。

Dim DatabaseFileName As String, connectionstring As String   

connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & DatabaseFileName & "; Persist Security Info=False;"

Dim conn As New ADODB.Connection
conn.Open connectionstring

Dim rs As New ADODB.Recordset, SQL As String
Dim ExecSQL As New ADODB.Command

With ThisWorkbook.Sheets(1)
    For I = 2 To 1235
        SQL = ""
        If .Cells(I, 7) <> "" Then
            SQL = "SELECT Count(VRSC_CUSTOMER_SITES.SITE_ID)  AS GCOUNT into [GVRCount1] "
            SQL = SQL & "FROM (VRSC_CUSTOMER_SITES) "
            SQL = SQL & "WHERE ((VRSC_CUSTOMER_SITES.SITE_STREET Like " & Chr(34) & .Cells(I, 7) & Chr(34) & ") AND ((VRSC_CUSTOMER_SITES.SITE_ST)="
            SQL = SQL & Chr(34) & .Cells(I, 5) & Chr(34) & ") AND ((VRSC_CUSTOMER_SITES.SITE_PHONE) Not Like ""999*""));"
            
            rs.Open SQL, conn
            
            SQL = "SELECT * FROM [GVRCount1]"
            rs.Open SQL
            
            .Cells(I, 8).CopyFromRecordset rs
            
        End If
    Next
End With
With ThisWorkbook.Sheets(2)

    .Range("A1").CopyFromRecordset rs
    
End With

conn.Close
End Sub

Answer 1

本质上，问题是由 LIKE 运算符引起的。每当您通过 ODBC/OLEDB 连接运行 Access 查询时，要使用的通配符是当前的 ANSI 版本 %。但是，在 Access GUI 中，通配符使用旧版本 *。见 MSDN docs讨论这种通配符的用法。

为了在 Excel 和 Access(VBA 或 GUI)之间兼容，请考虑将未记录的 ALIKE 运算符仅使用 %。此外，使用 ADO 命令使用 ADO 参数化并避免将值连接到 SQL 语句。下面将第一个 LIKE 替换为 = 因为没有使用通配符并且使用 INTO 的生成表操作已被删除。此外，从任何 Dim 行中删除 New。

Dim DatabaseFileName As String, connectionstring As String, SQL As String
Dim conn As ADODB.Connection, rs As ADODB.Recordset, ExecSQL As ADODB.Command
Dim I As Long

connectionstring = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" _
                    & DatabaseFileName & "; Persist Security Info=False;"

Set conn = New ADODB.Connection
conn.Open connectionstring

' PREPARED STATEMENT WITH ? PLACEHOLDERS
SQL = "SELECT COUNT(v.SITE_ID) AS GCOUNT " _
    & "FROM VRSC_CUSTOMER_SITES v " _
    & "WHERE v.SITE_STREET = ? " _
    & "  AND v.SITE_ST = ? " _
    & "  AND v.SITE_PHONE NOT ALIKE '999%';" _

For I = 2 To 1235
    If ThisWorkbook.Sheets(1).Cells(I, 7) <> "" Then
        Set ExecSQL = New ADODB.Command
        With ExecSQL
            .ActiveConnection = conn
            .CommandText = SQL
            .CommandType = adCmdText

            '  BIND PARAMETERS
            .Parameters.Append .CreateParameter("street_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 7))
            .Parameters.Append .CreateParameter("st_param", adVarchar, adParamInput, 255, ThisWorkbook.Sheets(1).Cells(I, 5))

            '  EXECUTE QUERY AND BIND INTO RECORDSET
            Set rs = .Execute
        End With
            
        ThisWorkbook.Sheets(1).Cells(I, 8).CopyFromRecordset rs
    End If
Next I

With ThisWorkbook.Sheets(2)
    .Range("A1").CopyFromRecordset rs
End With