Search code examples
ms-accessms-access-2016

MS-Access filter lookup option for ODBC linked table

I have a SQL Server table linked in MS-Access 2016.

The filter lookup options for fields in this table are not appearing, as expected:

screenshot showing only options to sort & search.

I have set the Database Options to allow Filter Lookup for ODBC fields, putting the max records to 1M (not necessary - there are <500K in the largest table), to no avail.

Access Database Options showing ODBC Fields checked.

I've Compacted, I've closed Access and re-opened, the fields are nvarchar(255).

I'm out of ideas. Does anybody have any?

Thanks!

Solution

  • OK, I found the answer after doing more digging in Stack Overflow: https://stackoverflow.com/a/10949856/13282028 . Turns out that the checkboxes would only appear if I reference a saved query in the Lookup settings, rather than using a SELECT query.

    This screenshot shows what doesn't work: Lookup settings using a SELECT query.

    This screenshot shows what does work: Lookup settings referencing a saved query

    All is well, but I wanted to share this to make it easier for folks in the future to find this answer.