Others

Fix Case-Sensitive LIKE Queries on Linked ODBC Tables in Microsoft Access

Elise WilliamsElise Williams Nov 22, 2026 868 views

Question details

The user needs a case-insensitive LIKE comparison against a linked ODBC table in Microsoft Access.

Fix Case-Sensitive LIKE Queries on Linked ODBC Tables 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().
Before you start

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.

Solution 1Recommended

Check the Access ANSI 92 setting and LIKE wildcard

Use this check to rule out a syntax mismatch before changing the linked source.

1
Open Access options

In Access, select File > Options > Object Designers.

2
Check SQL compatibility

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.

3
Review the query 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 %.

4
Test the query

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.

Check the Access ANSI 92 setting and LIKE wildcard
ANSI settings may not change the source comparison: The ODBC driver or external database can determine how linked text is compared. LCase() alone may not override those rules.
Free Microsoft Office alternative

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.

Review exported query results in WPS SpreadsheetsOpen and edit Microsoft Excel XLSX filesUse a familiar interface for a smoother transition from Microsoft OfficeAccess a free, lightweight office suite
microsoft office alternative - wps office

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.