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
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