Thank you for your feedback. I think I am going to move to the VBA method. I want to reduce the number of objects in my DBs and this will be a decent start.
I guess you didn't listen to the answer you got from ebs. There is no advantage to using VBA but there is an advantage to using querydefs.
At this point in the development of Access and the state of standard PC hardware, speed isn't going to be a factor unless you are running the SQL hundreds of times forcing Access to rebuild the same execution plan hundreds of times. There used to be a huge benefit to using querydefs due to the elimination of bloat. Creating an execution plan takes a lot of work space that that creates bloat but MS has figured out to minimize that so it is no longer the problem it once was.
So, that leaves it to personal choice. I have to tell you I am biased. I've been writing SQL for something in the neighborhood of 50 years and for the first 25 it was entirely by hand. Hard coded, embedded SQL. I used to dream of a tool like QBE although I would like it to be better than it is after 30 years. Let me list the problems I have with embedded SQL.
1. embedded SQL is not reusable unless you isolate it in submodules which no one ever does - querydefs are reusable.
2. If you change embedded SQL, it is changed in ONE place. If you want to change all copies, first you have to find them. Changing a shared querydef doesn't leave dangling unchanged queries. I sometimes create base queries that include certain types of calculated fields such as FullName which is a concatenation of first, middle, and last names and possibly the prefix and suffix also depending on the application. This makes it easy to just join this "base" query to a more custom query. Access figures it out when it creates the execution plan and optimizes the query correctly.
3. You have to run the code to test the query. With the QBE, you can run the queries outside of the code, especially if they get their arguments from form controls.
4. You have to either cut and paste a lot or have all the table names and column names locked in your mind so you can type them without mistakes. Personally, as a consultant, I work on too many applications at any given time to keep all the names straight, especially since I may not have built the tables myself. To me the QBE is a life saver. It is point and click.
5. An execution plan has to be created each time the query runs rather than once, the first time the query runs.
That gets us to the bad parts.
1. the QBE has this dreadful habit of rewriting your beautifully formatted SQL into unreadable strings with enough pairs of parens to choke a horse. This doesn't bother me. Why? I never look at the SQL. If I don't look at it, I won't be unhappy. In the cases where I do want to preserve the SQL strings, there is a trick. You just need to exercise some caution. Make sure you save the querydef from SQL view and refrain from changing to QBE view or if you do, be careful to cancel any request to save your changes otherwise, Access will rewrite your code and make you cry. I keep a table to hold certain SQL strings for just this purpose. Sometimes I have really complicated wHERE clauses with ANDs and ORs I carefully assign the parentheses so that the statement is evaluated as I intend it to be but Access just duplicates everything, making the query totally unreadable. Therefore, when I save changes to the querydef, I save the SQL string in my backup table so If I lose my mind and save in QBE view, at least I can get back the string as it was before Access broke it.
2. the QBE is as old as Access so it doesn't have any of the good features a modern version would have like comments. MS keeps promising a replacement but has broken that promise a dozen times so don't hold your breath.
3. The QBE has no good way to display subqueries