Access and Azure SQL Database
Hello, I tell you that I have the following problem, I connected my access database with an SQL database in Azure, but the communication is very slow, for example one of my forms takes 15 seconds to ...
Forum Discussion
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.
George_Hepworth Well there are many solutions and workarounds for this case..passthrough are read only but you can work with unbound forms and push the data to the server.
And there are others...beyond the scope of a simple question
"...but you can work with unbound forms and push the data to the server...."
Right, that's pretty much what I said. "It can be done but it's ugly."If performance becomes a work-stop issue, it's worth pursuing such options, but the amount of additional work required is a serious consideration as well. Besides, in my experience, the use of "heavy" forms laden with combo and list boxes and multiple subforms is a more common source of performance lags. That and the old method of binding a form to a table and then searching through that form's recordsource to find one record, that's a killer in terms of performance against a remote database.
Pass-Thrus have a place, along with other design considerations.