Jet and ACE make every effort to make every query a "pass though" query. i.e. send the query to the server and wait for the results to come back. There is certainly some overhead because Jet/ACE is involved but in exchange, you get to use standard (albeit a very old level of functionality) and bound forms. If you don't understand how this process works, you can get in the way and prevent Access from ever passing a query to the server. Instead, Access will send queries that request entire tables which it will then process locally. This is the worst possible scenario. You are getting NO benefit from having a "real" server based data store and you are flooding the LAN with unnecessary traffic.
I understand Issac's dissatisfaction with the QBE. However, since i also use saved querydef's almost exclusively, I don't have to look at what the QBE does to my lovely, formatted SQL. The whole point of the QBE is to give you a point and click interface. This is something I dreamed about from the early 80's when I first started working with DB2. The pain of having to type all SQL by hand was just a PITA. Plus, since I work with multiple clients at one time, I might have a dozen active databases in some state of construction and that means I have hundreds of tables and thousands of column names swirling around in my head when I go to type something. The QBE with all its flaws is a huge time (and error) saver should you choose to use it. So, my perspective on the whole SQL thing is totally different. I spent YEARS of coding SQL by hand and hating every minute of it, trying to keep all the table and column names straight and get the string correct in one compile rather than four. Being basically a lazy person, when I discovered Access in the early 90's and discovered that I could link to and update DB2 tables on the mainframe, I thought I had died and gone to heaven. All the tedium of having to create queries by hand and all that background work that Access does for you when you are using bound forms took the weight of the world off my shoulders and I could concentrate on the specifics of an application rather than the infrastructure. So a CICS transaction that might take me three days to code and unit test could frequently be done in a couple of hours using VBA and bound forms. And that's with me having lots of code libraries to pull common code from so I didn't always have to write it all from scratch.
It never ceases to amaze me that people actually want to write embedded SQL by hand. They don't want to point and click and build reusable querydefs. I have to admit, going from doing it all myself to the QBE was a much bigger step than going from COBOL where I did it all myself to VBA and bound forms. So, here's the bargain I made with the QBE, it builds the select clause -always and it builds any straightforward where clause. If the Where clause is too hard to understand in the QBE, I switch to SQL view and rewrite it without all the extraneous garbage needed to make the graphic view easy for MS to create. As long as I save the querydef in SQL View and avoid switching to QBE view and accidentally saving, I can have my cake and eat it too. Although, I copy these strings into a table to save them because if I make a mistake and Open the QBE for that query in Design view rather than SQL view, Access has a tendency to "help me out" and rewrite the SQL. However, if you carefully avoid QBE view for those queries, it doesn't mess with your formatting. And the table is my "safety play" (bridge term). If the where clause gets messed up, i don't have to retype it again. There is NO advantage that I can see for embedding static SQL in VBA. Period. I do create SQL using VBA but that is for situations where it is dynamic rather than static. ALL static SQL lives as a querydef. And perforce, all dynamic SQL is created with VBA although sometimes the basic SELECT part of the query is a querydef and only the WHERE clause and ORDER BY are built in VBA.
And one final benefit of sticking with plain vanilla Access SQL whenever possible rather than pass through queries and stored procedures, I can swap BE's in a heartbeat. I have a couple of applications that I sell as packages. They are very expensive and they are not "shrink-wrapped" so they come with a certain level of support including the client gets to choose the BE he wants and I only have to maintain one FE regardless of whether the BE is SQL Server, DB2, Oracle, or ACE. In one of the apps, I needed to make dual procedures because ACE is a little different in some areas from the big guys but all the big guys work with CodeB and ACE works with CodeA This code was very specific to the application. For training purposes, the app included a set of training data and since the user would update it each time they trained new users, I needed a way to restore it back to its original version so I kept backup tables in the FE with the test data. They were in the FE rather than the BE because they might change with each version of the FE and it was too hard to update the client's BE remotely. ACE allows you to insert rows with autonumbers populated but I had trouble with SQL server. I needed to set Identity Insert off and on to put back the original data including their PKs.
Using any multi-table recordsource, whether a t-sql View or an Access query, often makes it non-updateable.
Only if the tables do not have primary keys or if you have done something funky. Most queries used as the RecordSources for bound forms will only have ONE table that gets updated. Others are there for lookup purposes only so they never cause Cartesian Products. I have queries with a dozen or more joins that are updateable because they don't break any of the rules that would make them not updateable.