MS Access query: why does LIKE behave differently when being called from VB6 app?

I don’t do a lot of coding with VB6, but I’m updating an existing app now and just encountered a snag.

I figured out the problem. In VB6, queries must use the % wild card when using LIKE, but in MS Access, you have to use the ***** wild card.

I’m querying the same database – (it’s in MS Access).

When querying from within MS Access, the following query works:

SELECT * FROM table WHERE field LIKE '*something*'

when I build that query in VB6, I have to do this:

SELECT * FROM table WHERE field LIKE '%something%'

What’s happening? Is that normal?

Answer

Access used to have its own incompatible version of SQL, so I think it uses the * for legacy reasons.

When you use VB6 you usually use ODBC and a more standardized SQL, so the more common wildcards apply. Remember that VB6 doesn’t care which DB you use, so if you used something else (e.g., SQL server) it would probably only understand the percentage signs.

I am guessing that the Access-ODBC connector converts things for you.