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. Advanced > Clear All Filters Advanced > Filter by Form Enter filter criteria into (bound) text boxes, for instance 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!

Using filter by form in Access taking way longer than usual

Kevin McCartney 0

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.

Advanced > Clear All Filters
Advanced > Filter by Form
Enter filter criteria into (bound) text boxes, for instance
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!

Kevin McCartney 0 Reputation points

2023-02-08T18:58:34.23+00:00

see next
Kevin McCartney 0 Reputation points

2023-02-08T19:01:11.2066667+00:00

Additional things I've tried:

Rebuilding indexes

Putting the SQL statement into a Query for the form's RecordSource

Creating a basic search form that opens the problematic search form with:

DoCmd.OpenForm "APD_Form", WhereCondition:=sSql

Running the filter by form on someone else's PC

--- none of these have improved performance...