Microsoft Jet wildcards: asterisk or percentage sign?

Question

What is the proper multi-character wildcard in the LIKE operator in Microsoft Jet and what setting affects it (if any)? I am supporting an old ASP application which runs on Microsoft Jet (on an Access database) and it uses the % symbol in the LIKE operator, but I have a customer who apparently has problems in his environment because the % character is understood as a regular character, and I assume that his multi-character wildcard is *. Also, I'm almost sure that in the past I have written application with queries using * instead of %. Finally, Microsoft Access (as an application) also works only with * and not % (but I'm not sure how relevant it is).

I just spent about 20 minutes searching the Internet without any useful results, and so I thought it would be useful ask on stackoverflow. Somebody may already know it, and it's better to keep the potential answers on stackoverflow than any other random discussion forum anyway.

Ludovico

Answer 1

The straight answer is that the behaviour of the wildcard characters is dependent on the ANSI Query Mode of the interface being used.

ANSI-89 Query Mode ('traditional mode') uses the * character, ANSI-92 Query Mode ('SQL Server compatibility mode') uses the % character. These modes are specific to ACE/Jet and bear only a passing resemblance to the ANSI/ISO SQL-89 and SQL-92 Standards.

The ADO interface (OLE DB) always uses ANSI-92 Query Mode.

The DAO interface always uses ANSI-89 Query Mode.

When using ODBC the query mode can be explicitly specified via the ExtendedAnsiSQL flag.

The MS Access user interface, from the 2003 version onwards, can use either query mode, so don't assume it is one or the other at any given time (e.g. do not use query-mode-specific wildcard characters in Validation Rules).

ACE/Jet SQL syntax has an ALIKE keyword, which allows the ANSI-92 Query Mode characters (% and _) regardless of the query mode of the interface, however has the slight disadvantage of the ALIKE keyword not being SQL-92 compatible (however ALIKE remains highly portable). The main disadvantage, however, is that I understand the ALIKE keyword is not officially supported (though I can't imagine it will disappear or have altered behaviour anytime soon).

Maros

Answer 2

If you're using DAO, use asterisk (and question mark for single symbol placeholder). If you're using ADO, use percent sign (and underscore).

Aggregation

Answer 3

You may find this useful:

http://msdn.microsoft.com/en-us/library/aa140104(office.10).aspx

In the query design grid and with DAO you use *, with ADO and ASP, you use %

Goetz

Answer 4

Accessing Jet via ODBC, it's not clear to me what wildcards should be used. I'd assume the natural Jet SQL ones (*/?), but since I never use Jet data via ODBC I can't say.

Within Access, as the article Remou cites says, in code, it depends on what data access interface you use -- ADO (which nobody should be using from within Access), you use %/_ , while with DAO (Jet's native interface layer), you use Jet's native wildcards (*/?).

There is an exception in later versions of Access executing SQL within Access. I don't know if it was Access 2002 or 2003 that added it, but nowadays, there is an ANSI SQL 92 compatibility mode that you can turn on. If you do that, the wildcards become %/_ instead of */?. But being able to choose the ANSI mode applies only within Access itself -- as onedaywhen has informed us, you have to choose different data interface libraries to use the different SQL modes, with ADO using the 92 and DAO and ODBC 89.

Berber

ACE/Jet has two sets of native wildcards: one native set for ANSI-89 Query Mode and another native set for ANSI-92 Query Mode. These query modes, being native to the engine, do not apply "only within Access itself". – Maros

How do you set ANSI mode when running SQL against Jet? I really don't know, as I never use Jet except through Access itself. – Berber

For Access2007, see office.microsoft.com/en-gb/access/HA100766011033.aspx (under section 'Find which ANSI standard a database supports'). – Maros

I don't know of any interface into Jet/ACE which lets you choose ANSI Query Mode... except MS Access -- depends what is meant by 'interface'. I suspect the Access user interface uses OLE DB as the interface to ACE/Jet when in ANSI-92 Query Mode (I used the word 'interface' to mean different things). – Maros

ADO always uses ANSI-92 Query Mode, even the 3.51 OLE DB Provider against a Jet 3.51 .mdb. DAO always uses ANSI-89 Query Mode. So if you wanted you ACE/Jet-not-MS-Access application to support both you could implement queries in both ADO (or other OLE DB) and DAO. – Maros

I have two Access databases next to each other used by the same application via ADO (in the save environment and settings), and they behave differently. I’ll post more once I know more. – Ludovico