How do I make wildcards work in sql thru ado?

I've got a tricky problem, I can't fix it in msdn, so what to do? time to come to you gurus here at vbforums :D

The situation is I'm accessing an access 2000 (jet) database from vb6 using ADO. when i try and open a recordset based on this string:

SELECT Files.[FileID], Files.[File Name], CDs.[CDID], CDs.[CD Name] FROM CDs INNER JOIN Files ON CDs.CDID = Files.CDID WHERE ((Files.[File Name]) Like 'gladiato*');

it returns 0 records. If I run the same string directly in an Access query it works fine and i get 1 record 'gladiator'. also if i change the 'gladiato*' to 'gladiator' then it works thru vb.

It seems to me that i can't use wildcards in the sql string from vb. does anyone know if this is correct, and if it is, does anyone know a way around it? my program sort of revolves around this working, it's a searching program :(

use % not *

Code:

---

SELECT Files.[FileID], Files.[File Name], CDs.[CDID], CDs.[CD Name] FROM CDs INNER JOIN Files ON CDs.CDID = Files.CDID WHERE ((Files.[File Name]) Like 'gladiato%');

---

:)

OK, while i was writing my last post i finally realised that the wildcards were the problem. So, stupidly, I searched vbforums for sql wildcards after hitting the submit button. and promptly found out that ado uses % instead of *, and _ instead of ?. So problem solved, sorry to have bothered everyone with such an easy question.

:)

OK. That makes a new problem. I am searching file names in my database, and filenames can have % or _ in them so what is the override-type thing in ado so that it will search for a % or _ instead of treating them as wildcards?

I am not sure but maybe %% and __ instead?

Franky

No, I think you have to use brackets around the wildcard symbols if you want to search for them.

SELECT * FROM FileTable WHERE FileName LIKE '2002[%]04[_]%'

will give you files with the following mask

2002%04_ABC.DOC
2002%04_DEF.DOC
2002%04_TEST.DOC
.
.
.
:)

:D Thanks All
peet - you were right the [ ] do the trick