Using filter by form in Access taking way longer than usual

Kevin McCartney 0 Reputation points
2023-02-07T23:40:33.48+00:00

We have Access with Microsoft 365 Apps for business as the frontend to SQL Server 2017.

One of the forms in Access is routinely searched by using filter by form (Ribbon > Home > Sort & Filter > Advanced > Filter by Form). Now performing these steps is taking extraordinarily long and it's not clear to me that anything has changed other than the number of records returned.

  1. Advanced > Clear All Filters
  2. Advanced > Filter by Form
  3. Enter filter criteria into (bound) text boxes, for instance
  4. Toggle Filter

The RecordSource of the form is a SQL statement that's long and contains 9 left joins. It's not pretty, but it has previously worked more quickly.

Unfiltered, the recordset is ~17,500 records. In SSMS the query takes 2 seconds to return. With Filter by Form it's now taking 2+ minutes.

The VM on which SQL Server runs shows performance as normal through Task Manager.

Thoughts? Much appreciated!

Access
Access
A family of Microsoft relational database management systems designed for ease of use.
400 questions
{count} votes

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.