VBA SQL - difficulty with delimiters and WHERE clause

[Gregg K]

I'm using Office 2003 on Xp sp2.

Please notice that this VBA code works and produces
a record set which contains the desired data if the WHERE
clause is omitted. When, however, the code is run as presented
below, the recordset remains empty. There definitely is the
correct data eg. "Cat" in the Table1.Table1_Name field of
the testData.mdb database. Am I doing something wrong with
the delimiters around 'C*' in the SQL?

Private Sub Command5_Click()
 
Dim rs0  As New ADODB.Recordset
Dim AccessConnect As String
 
' the connection string
AccessConnect = _
"Driver={Microsoft Access Driver (*.mdb)};" & _
"Dbq=testData.mdb;" & _
"DefaultDir=C:\test;" & _
"Uid=Admin;Pwd=;"
 
' the query string
QueryString = "SELECT Table1.Table1_Name FROM Table1 " & _
"WHERE Table1.Table1_Name LIKE 'C*';"
 
Debug.Print QueryString
 
rs0.Open QueryString, AccessConnect, adOpenStatic
 
rs0.MoveFirst '<- error is generated if recordset is empty
 
rs0.Close
 
End Sub

Any help is appreciated.

Gregg

[Killer42]

You'll find much more information about this kind of thing in the Access forum. But I think (not certain) that replacing the "*" with "%" might help.

[Gregg K]

How correct you are, on both counts. Thank you, thank you.

For anyone else wondering about DAO / ADO SQL wildcard differences:
look here

[Killer42]

How correct you are, on both counts. Thank you, thank you.

Glad to be some help. :)

I'll pass along that link to the Access forum Moderator(s), too.

[NeoPa]

How correct you are, on both counts. Thank you, thank you.

For anyone else wondering about DAO / ADO SQL wildcard differences:
look here

Thank you Gregg, that's a very interesting link that I'm happy to see used.
However, you should be aware that the wildcard character sets are differentiated, not by using Access or not, but on the ANSI-92 compatibility.
From version 2003, Access has the option to be compliant with this standard (%;_;^; etc rather than *;?;!). ADP & T-SQL from SQL Server, are already compliant as standard. It's rare in my experience though, even now, to get Access database questions where the OP has the standards applied mind you.
I'm not sure about ADODB, it may default to the ANSI-92 standard too, which would explain your situation.