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

Question

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 1

I've never seen the asterisk character used as a wildcard for a like statement (just everywhere else) -- generally speaking the percentage sign is what you would need to use.

Answer 2

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.

Answer 3

Yeah, that's normal.

I think its the difference between DAO (what Access uses internally), and ADO (what VB6 uses to talk to Access).

Answer 4

Access will use a subset of ANSI-89 wildcards by default, VB6, connecting through ADO will use ANSI-92.

Operator Comparison

Changing the mode Access uses

Answer 5

I don't know if this applies to VB6, but within Access, you can use

ALIKE '%something%'

and the % characters will be treated as wildcards regardless of whether you're using VBA with DAO or ADO, or creating a query in the query editor.

Answer 6

Yes, you can get away with ALIKE in a Jet 4.0 OLE DB inquiry (i.e. from VB6 using ADO):

JeTTY version 0.5.68
>open booksale.mdb;
#Opened database booksale.mdb (Jet3X "97")
>select * from authors where author like "ba*";
#No rows to display
>select * from authors where author like "ba%";
               Page 1 of 1
Au_ID Author     Year Born
───── ────────── ─────────
10    Bard, Dick 1941
>select * from authors where author alike "ba%";
               Page 1 of 1
Au_ID Author     Year Born
───── ────────── ─────────
10    Bard, Dick 1941
>

Of course you gain compatibility with Access but then lose ANSI SQL-92 compatibility for later upsizing to SQL Server, etc. and ultimately make more work for yourself.