SQL Views vs MS Access Queries

Question

I used the SQL Server Migration Wizard to transfer all my tables to the backend MS-SQL Server. Other objects like forms, reports and queries are still in my Access Front-End. I was thinking of converting my complex queries containing multiple tables and join statements to pass through queries but I some discussion posts suggest storing queries in the SQL Server instead of using pass through queries or leaving the queries in MS Access.

Why is having queries on the SQL Server a good idea? Is it really better than using pass through queries?

Answer 1

Using a pass-through query (PT) or a view means that in both cases the SQL is server side. So not really a difference in performance between using PT query, a view and calling a stored procedure from Access.

The main downside of a PT query is they are read only. So VBA code, and forms can’t update something based on a PT query.

However a view is updatable and performance is the same.

If a form is to be bound directly by a table link to a table on SQL Server (say with 1 million rows), then such a setup is fine IF YOU ALWAYS open the form using the standard VBA where clause when launching that form.

Because a access form is based on one table (and one record to edit), then you gain zero performance in this case by using a PT query, a view, a stored procedure or just a standard linked table to SQL Server (there is no performance advantage).

However in your question you mention “complex” SQL with multiple tables/joins etc.

In this case, a PT query is fine for a report, and possible some form that “displays” data for searching etc. However the form for editing has to be a view, or linked table. A PT query as noted is read only.

Since 99% of forms are based on one table, then continued used of a linked table to SQL Server will perform just fine (just remember the above rule to always open such forms to one record).

So a PT query and a view are really much the same thing – they both run server side. The only real difference is that you can’t store a PT query server side, but you can a view. So a “view” is really much like a local saved query in access – and it read/write as compared to PT = read only.

So for a report, you can go through the hassle of setting up a pt query, but for parameters and filtering, you find a view is a much better choice. (Because if you have existing VBA filter code for that report – it can remain untouched, and only the records that satisfy the filter (the where clause – not an actual report filter) are respected.

Note that Access does a rather good job if the form or report is based on one table (a linked table). In this case, Access only pulls down records that meet your provided “where” clause for the report or form when you open it.

What this means then is for a report based on a complex query in Access you convert the query to a SQL Server side view, and now link the report to that view in place of the query. This is nice since as noted your filters will work as before, and you don’t go through all the hassle of setting up a PT query which tends to be more of a hassle then creating that view.

So a PT query is not better nor faster than a view but the view has several advantages. It tends to mean no code changes in the Access client side, and as noted a view is read/write as opposed to a PT query being read only.

So both PT and views run server side, but for ease of development and the fewest changes in Access side code, then a view tends to be a far better choice, and besides, as noted a PT query is not stored server side, but certainly runs server side when you send the PT query to the server.