Funny, I ALWAYS use querydefs unless my SQL is actually dynamic as it might be with a complex search form.
WHY?
1. they are easier to create and test. You get help with intellisense when using the QBE.
2. they can be reused. So, I frequently create a "base" query and then use that as a shortcut to build a new query. This is not less efficient since Access pulls in the dependent queries when building the execution plan and builds the most efficient query that incorporates all the queries of the high level query.
3. they are more efficient. The execution plan is calculated and saved the first time the querydef is executed. This eliminates one small bit of time that gets added when you use embedded SQL. For embedded SQL, Access must create an execution plan each time you run the query. Also less critical in newer versions of Access since Access has gotten smarter and one of the really bad things isn't so bad any more. The calculation of execution plans used to create enormous database bloat in the FE so even the FE needed to be regularly compacted as well as the BE. Access now cleans up after itself so running embedded SQL is less bad than it used to be.
4. you can use the integrated documentation feature to map where querydefs are used so you have a good overview of what needs to change if you need to change something in a table. If all your SQL is embedded in VBA, you're on your own.
I'm not saying that the QBE is a fabulous tool. In fact, i is quite weak but despite its weaknesses and even downright annoyances such as its obsession with parentheses and destroying all your beautiful SQL formatting, it still saves a great deal of time.
I always find the discussion between the use of the QBE and using embedded SQL to be curious. I've been writing SQL since the late 70's so it's not like I don't know how to do it yet. It's more like, been there done that, don't need the practice. I used to dream of having a tool like the QBE, long before there was a computer on every desk. Having to remember all the table and column names for sometimes a dozen databases at a time is just too much for my pea brain. Especially if I didn't create the database so I'm dealing with someone else's idea of a "standard". Maybe it's because I've spent most of my career as a consultant and so I frequently support multiple clients with multiple applications at one time or move from assignment to assignment and so frequently face new databases that I didn't build. With the QBE, I point and click and never worry about spelling or typos. Granted, if your selection criteria is complex, the QBE can be awkward so I might switch to SQL view after the select and joins are built. The QBE is also poor if you are building subqueries but since Access is so inefficient with subqueries, I just never use them unless there is no other option. That leaves me with very few queries that the QBE doesn't display effectively. So, I don't have to look at the hot mess that the QBE makes of my SQL. And if I don't have to look at it, it doesn't bother me
Here's a secret for those of you who love beautifully formatted SQL, You CAN prevent the QBE from mucking it up. Once you switch to SQL view, ALWAYS save in SQL view and never go back to QBE. I also keep a table where I can paste formatted SQL just in case I lose my mind and switch to QBE on one of the queries the the QBE is going to mess up. That way, I can just get the nicely formatted version from the table and paste it back in the QBE.
For my complex search forms, most of the time the Select clause is static so I use a querydef for that and just build the Where clause on the fly.
for a basic-level user who is accustomed to the capitals and the existing names.