LOL - my thoughs about the network concern, exactly.
I am lucky, our offices throughout the country do have really bad networking.
By going to Citrix, one VM server is sitting in the same box as the VM SQL Server.
So, all the network traffic is very self contained.
When I am in a hurry to get something out, I might just drag it all over.
In some cases, it is hard to justify enhancing every situation with a pass-throughs.
That said, the creation of SQL Server Views to process the data does increase the efficiency - in some cases.
This is worth reading - and using the SQL Profiller (SS Profiler) is sometimes worth the effort.
http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/56c36d96-8cd9-4da4-9cfc-64231ffaec94/
Since Access 2007, the Where clause adds huge efficiencies (well, it depends) when connecting to SQL Server. Even for two tables (if they have a relationship).
They also discuss using a query with a Where clause then "topping off" with MS Access using the unique IIF, Nz... ect. There are some very, very gorry details in this article. I will sum up with some quotes at the end:
You really don't need to use parameters, and it's just going to result in a bunch of expensive coding and programming time that yields you in 99 percent of the case nothing at all. You don't need to use ado, you don't need to use ADO + parameters, and you do not need to use a PTQ either. In fact you can use bound forms and use the access standard built in where clause.
You can use a bound form and bind it to a table of a million records, you can then execute an open form command with a where clause to a particular ID. You will find that only the one record comes down the network. This is true if you using an linked table to oracle, a linked table to SQL server, a linked table to mySql. In fact only the one record will come down even when you're not using ANY SERVER of any type but using a standard back end mdb file share and an index can be used.
- this is most likely referencing Access 2007 and beyond.
My experience in Access 2003 (not on Citrix) indicated SQL Views worked faster wth a single parameter.
So this idea that all records are pulled down when you use where clauses and build parameters on the fly is one of those things steeped in great mythology born of those who read about propaganda about Access. This is much like one of those urban legends where the little boy flushes his pet alligator down the toilet. It then begins to live a life in the sewer system, grows to become huge proportions, and then begins to attack maintenance workers in the sewer system. So just like the urban legend of alligators and the sewer system, it is an urban legend that access will pull down all records when you use linked tables and bound forms. This where clause issue is also the same and works equally well for reports .
In the case of a linked view, then you can in code go:
dim strSql as string
strSql = "select * from view_custOweing where custid = 123"
set rst = currentdb.OpenRecordset(strSql)
And for reports, again the view + openreport works well. In fact, it means you do not have to modify and rewrite existing code, and that much why I suggested views.
Once again SQL server does a very good job of taking the SQL that Access generated on that view plus a couple parameters tossed up by access in a where clause. That sql gets set to the server, and only the one record (or required records) will only come down the pipe.
"Gasp!!! Someone might want to actually use the precious server!!! It would result in 'network traffic'!!! Gasp!!!" 