sql wildcards not working

[davers5]

I'm trying to look up values from an excel sheet in an access database. The wildcards for the sql statement seem to not be working. If I use the sql statement: SQL = "Select * From Contacts WHERE Name like 'Sandman, Richard';" a record is returned. But when I use this: SQL = "Select * From Contacts WHERE Name like '*Sandman*';" then there's an empty recordset. Even this: SQL = "Select * From Contacts WHERE Name like '*';" returns an empty recordset. Am I doing something wrong? Below is my full code.

Thanks,
Dave

Private Sub RunProgram()
Dim n As Integer
Dim strName As String
Dim SQL As String
Dim strCon As String
Dim strSourceFile As String

strSourceFile = "C:\Documents and Settings\Dave\My Documents\SCORE\ScoreData\SCORE_be.mdb;"
strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strSourceFile

Set cn = New ADODB.Connection
cn.Open strCon
Set AdoRs = New ADODB.Recordset
n = 0
Do Until Sheet5.Cells(n + 2, 5).Value = "" And _
Sheet5.Cells(n + 3, 5).Value = ""

strName = "*" & Sheet5.Cells(n + 2, 4).Value & "*"

SQL = "Select * From Contacts WHERE Name like '*';"
MsgBox SQL
AdoRs.Open SQL, cn, adOpenDynamic, adLockPessimistic
If AdoRs.EOF And AdoRs.BOF Then
Sheet5.Cells(n + 2, 2).Interior.Color = vbRed
Else
Sheet5.Cells(n + 2, 1).Value = AdoRs!ContactID
Sheet5.Cells(n + 2, 2).Value = AdoRs.RecordCount
End If
'Else
'Call LoadEventData(AdoRs!ContactID)
AdoRs.Close
n = n + 1
Loop

cn.Close
End Sub

 

[dk]

Hi,

The wildcards in ADO are different than in Access/Windows :

Use % instead of *
Use _ instead of ?

 

[davers5]

Thanks for that simple solution. I've worked a lot with ADO but I guess I've never used wildcards (I know it's hard to believe) until now. It's definitley necessary to know this!

Thanks again,

Dave