Sanya Sanya - 2 months ago 6x
SQL Question

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

i = 0

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

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

With Me.list_SearchJobs
.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

'Close the recordset and database connection

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

'Close the recordset and database connection

'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


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