Fix Case-Sensitive LIKE Queries on Linked ODBC Tables in Microsoft Access
Question details
The user needs a case-insensitive LIKE comparison against a linked ODBC table in Microsoft Access.

- Product
- Microsoft Access and InterSystems Caché ODBC
- Device & OS
- not provided
- Scenario
- An Access query searches text in a linked InterSystems Caché table, while the same query is also tested against an imported local copy.
- Observed behavior
- The linked-table query matches case-sensitively even with ANSI 92 disabled, while the query works as intended on the imported local table. Enabling ANSI 92 returns no results, even with LCase().
Identify the linked table's ODBC source and test the same search term against a local copy. Note the wildcard used in the LIKE pattern, because Access wildcard syntax depends on the ANSI setting.
Check the Access ANSI 92 setting and LIKE wildcard
Use this check to rule out a syntax mismatch before changing the linked source.
In Access, select File > Options > Object Designers.
Under SQL Server Compatible Syntax (ANSI 92), check whether the option for the current database is disabled, as required for the original Access-style pattern.
Open the query in Design View or SQL View and check its LIKE pattern. Access-style syntax typically uses * for multiple characters, while ANSI 92 syntax uses %.
Run the query with search terms in different cases. If the linked table remains case-sensitive while the local copy does not, investigate the source-side comparison rules.

Apply case-insensitive matching at the ODBC source
Use a source-side query or function when the linked database controls text comparisons.
Import the ODBC data into a local Access table
Use a local table when a snapshot is acceptable and Access comparison behavior meets your needs.
Work with exported query results in WPS Office
WPS Office is a free, lightweight alternative for working with office documents and spreadsheets. Its familiar interface and Microsoft Office format compatibility make it easy to move exported Access query results into a spreadsheet workflow. It does not change Access or ODBC comparison rules.

Frequently Asked Questions
Why does the same LIKE query behave differently on a linked table?
The linked ODBC source or its driver may apply different text-comparison rules from the local Access table. Test the same search against both tables to isolate the difference.
Where do I find the ANSI 92 setting in Access?
Select File > Options > Object Designers, then look for SQL Server Compatible Syntax (ANSI 92). Check the setting for the current database.
Why did enabling ANSI 92 return no results?
The LIKE pattern may still use Access-style wildcards. ANSI 92 typically uses % for multiple characters instead of *. The external source's comparison rules can also affect results.
Can LCase() make a linked ODBC query case-insensitive?
Not necessarily. The source or driver may enforce comparison rules that LCase() does not override. Test a source-supported case-insensitive function or collation instead.
Will an imported Access table stay synchronized with the ODBC source?
No. An imported table is a local snapshot. To obtain later source changes, repeat the import through External Data > New Data Source > From Other Sources > ODBC Database.




