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).
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…