Forum Discussion
Filtering records from SQL Database
When I run a query from MS access against a SQL server database, the result (Number of Records) are placed in the Record Navigation Bar. Similarly, if this query is linked to a Form, the Filtered records can be changed by the 'On ApplyFilter' event on the form.
The same thing happens, the number of records returned by the Filter is returned in the Record Navigation Bar, and the response time is very bad.
I can mention that if the query is linked to a MS Access Back End Database (.MDB File), the response times is instantaneous
I need help please. . .
Petran5 What you describe is typical of queries run against SQL Server databases, particularly "remote" databases. This is due, in part to the latency of connections involved between the local accdb and the remote database. For that reason, most experienced Access developers avoid loading all records into a form at any time unless it is absolutely required for a very specific task.
In your case, the workflow is, unfortunately, quite common in Access,
"...the Filtered records can be changed by the 'On ApplyFilter' event "
That's the way most of us learned, but it is exactly opposite to good practice when querying SQL Server databases. One should apply parameters in the query itself to return only the smallest number of records absolutely needed.
See these resources for more information. Both are older but still 100% relevant.
The Best of Both Worlds: Access SQL Server Optimization
