Forum Discussion
Access and Azure SQL Database
It's certainly true that using Pass-Thru queries can help, at least in part and at least in some aspects, but it's not quite that simple over-all.
For one thing, Pass-Thru queries are read-only in Access. That means they can be effective in retrieving large or complicated recordsets for reports, and for places in forms where you do NOT need to update the recordset. It also means you can't use them for data entry operations without a lot of additional processing, which is generally not all that fun to do.
For another, the problem of performance against a remote SQL Server or SQL Azure database is not limited to queries per se. Other kinds of commonly implemented "Old School" Access relational database application designs are problematic when the Accdb BE is replaced by the remote database. Multiple combo or list boxes on a form, for example, and multiple subforms in a form also drag down performance regardless of the use of local or Pass-Thru queries.
While it can be a very effective way to deploy an Access solution for remote users, using a hosted SQL Server or SQL Azure database requires a total rethink of the ways we design the interface.
In short, the goal of "New School" Access relational database application design should be to limit --to the extent possible-- both the absolute number of records in any recordset and the number of recordsets that must be returned.