Excel VBA SQL from Access no results

Using Excel 2010 to query an Access 2010 Database (via UserForms).

When I execute the code, I get my Message Box “No Results” (called near the end of the sub). However, there should be 12 records that pull up when I enter in a certain search string.

I thought maybe my SQL string was incorrect so I wrote the SQL statement to Sheet1 Cell A2. I then opened up my Access Database, created a SQL query, and copy/pasted the SQL statement from cell A2 – It worked perfectly. –> So it’s not the SQL statement.

Why is my code not finding the data? The SQL statement works fine. I’m not getting any errors when I try to establish an ADODB connection.

EDIT: I use the exact same database connection setup in another sub and it works fine.

Private Sub searchAllInJobs(searchStr As String)
    Dim con As Object, rs As Object, accessFile As String, strTable As String, sql As String, keyStr As String, i As Integer

    accessFile = "******************"  '<--hidden on purpose

    Set con = CreateObject("ADODB.connection")

    If Err.Number <> 0 Then
        MsgBox "Failed database connection", vbCritical, "Connection Error"
    Exit Sub
    End If

    On Error GoTo 0

    con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & accessFile

    'Wild card string
    keyStr = """*" & searchStr & "*"""

   'I have tested this SQL statement in the Access database and works great
    sql = "SELECT * FROM tbl_job_tables WHERE ([MODELNO] LIKE " & keyStr & ");"
    'Write the SQL to a cell, to make sure the statement was constructed properly
    Sheets("Sheet1").Range("A2").value = sql

    Set rs = CreateObject("ADODB.Recordset")

    'Open the database recordset from the SQL statement
    rs.Open sql, con

    'Clear the current ListBox
    Me.list_SearchJobs.Clear

    i = 0

    If Not (rs.EOF and rs.BOF) Then
        'Move to the first item
        rs.MoveFirst

        'While going through the records, if you haven't reached the End-of-file then...
         Do While Not rs.EOF

            With Me.list_SearchJobs
               .AddItem
                   .List(i, 0) = rs!JOB_NUM
                   .List(i, 1) = rs!customer
                   .List(i, 2) = rs!MODELNO
                   .List(i, 3) = rs!CREATE_DATE
            End With
            i = i + 1
            rs.MoveNext
         Loop

         'Close the recordset and database connection
         rs.Close
         con.Close

         'Set the objects to "Nothing" (clears the cache)
         Set rs = Nothing
         Set con = Nothing

    Else
        'Close the recordset and database connection
        rs.Close
        con.Close

        'Set the objects to "Nothing" (clears the cache)
        Set rs = Nothing
        Set con = Nothing

        MsgBox "No Results", vbCritical, "No results"
    Exit Sub
End If
End Sub

Answer

I think I know why this is happening. The wildcard in Access is *, but for most other variants of SQL it is %. You are using ADO here. See this

Try this instead:

keyStr = "%" & searchStr & "%" 'Not sure if you need the extra "'s either

Advertisement

Answer