SQL query does not work in Excel but works in Access-CodePudding

For the code listed below, it runs fine except for the first SQL query. I'm pulling address and state information from the workbook, and running a query on the information to find the count of how many times the address appears in the table. If I run the code and stop it before the query is sent to Access, I can pull the query command from the Immediate window, go to Access, and run the query no problem. However, if I just run the VBA program and have it send the query to Access, I keep getting 0 for the result. So long story short, the query will run in Access and provide the correct result, but when Excel VBA sends the query to Access, I keep getting zero for the result (and no error messages). Any help would be greatly appreciated.

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

CodePudding user response：

Essentially, the issue is due to the LIKE operator. Whenever you run an Access query over an ODBC/OLEDB connection, the wildcard to use is the current ANSI version %. However, in Access GUI, the wildcard uses the older version, *. See MSDN docs discussing this wildcard usage.

To be compatible between Excel and Access (VBA or GUI), consider undocumented ALIKE operator to only use %. Additionally, use ADO parameterization using ADO command and avoid concatenation of values to SQL statement. Below replaces the first LIKE with = since no wildcard is used and the make-table action using INTO was removed. Also, New is removed from any Dim lines.

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