SQL query does not work in Excel but works in Access

Question

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

Why do you open rs the first time? You don't use it and throw it at once away when opening the second query. Makes no sense to me. — Gustav
– Gustav, Commented Dec 30, 2021 at 21:51
Yes you are correct. So please disregard that part. I have since deleted the "SQL = "SELECT * FROM [GVRCount1]"" and the "rs.Open SQL" part. So I'm basically just looking at the SQL stuff. I'm still getting the same issue. — jmwillithree
– jmwillithree, Commented Dec 30, 2021 at 22:15
I have had to troubleshoot SQL via ADODB like this before and I would recommend starting with a "no fail" SQL statement like SELECT * FROM [VRSC_CUSTOMER_SITES$] first to see if it's the SQL or your connection. If that works, I would comment out and test your more complex SQL statement line by line un-commenting it (with modifications to make it still work). I don't query against MS-Access ever, but when I write my SQL queries to go against an Excel workbook (used as a database), I have my tables in SQL written as: [Table$] with the "$" at the end. Double-check? — ScottyJ
– ScottyJ, Commented Dec 30, 2021 at 22:29

Parfait · Accepted Answer · 2021-12-30 22:30:45Z

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

Thank you @Parfait and wackojacko1997. Parfait you were indeed correct about the percent operator. All of my data used "" as the wildcard character. When I changed the "" to a "%", my query worked. My simpler queries worked (per wacko's recommendation), so Parfait your suggestion made sense. Thanks guys for all of the help!

Collectives™ on Stack Overflow

SQL query does not work in Excel but works in Access

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related