The explanation was clear so that was nice but it still left me with a couple of questions.
1. He didn't mention whether or not the subform was updateable or if he was using the master/child links. If you've used the technique, you can tell us the answers to those two questions. I think if the subform is updateable, you need to be able to use the master/child links or Access won't populate the foreign key and you would have to code that yourself. It might be possible to set the master/child links if you have linked tables and then remove the links. I don't know if that breaks the form when you remove the linked tables though.
2. The sample assumes that the BE never changes. That means you don't get a test or QA or production database, just one to use for everything - really poor practice. I had one client with EIGHT staging databases and as the application moved through the validation process, it moved from one level to another until it finally got to production so the sample should use a variable for the connection string. I keep the connection strings in a table so that I never have to change code like this. The connection string that is "live" is stored in a separate table so that one is used by any code that requires a connection string. Most big clients require me to have at least three BE's. For little clients where I am the DBA, I use at least two but usually three. That gives me one for me, one for the users, and one for production. Once I've released a version for test, the users can test it in QA and I can move on with the next installment and we don't interfere with each other. If you let them test with YOUR data, they will continue to clobber it so you never know what you have and you might clobber their test so if you want to be able to overlap design stages, you need at least three databases - even when the BE is ACE!!
3. As he mentioned, you can't use references to querydefs so that means you have to expand the SQL to use subqueries if you need them. I nest querydefs all the time. Access puts them together for me as it passes them to the server for execution.
4. What he didn't mention was that you would need to use T-SQL syntax rather than Access syntax where they are different.
5. Using this technique, all your SQL is embedded. This means that you have to remember hundreds or thousands of table/column names depending on how many applications you work on a one time. As a consultant, I might have a bunch of applications at multiple clients that are "currant" An employee generally has a much smaller scope of applications to worry about at any one time. Personally, when I started using DB2 back in the early 80's, I used to dream about a tool like the QBE. At that time I was writing my embedded SQL in COBOL and having to deal with all the memory/typo issues of dealing with tables that I didn't necessarily create so I had no control over how columns were named and whether or not they were consistent. At one client, the customer was called "Cust" at another, he was called "Client". So, if this is Tuesday, I must be in Belgium. So, now that I have such a tool, I'm not inclined to give it up. Even though I know how to write SQL myself, I still use the QBE for at least the basic parts of the query. If I have complex criteria, I might switch to SQL view to type it but all static SQL is done with querydefs. Dynamic SQL is obviously done using VBA. But my dynamic SQL is almost always just for complex search forms. Sometimes Access is just too helpful when it reformats my querydefs so for some very complex ones, I store the SQL string in a table so I can get it back if Access "helps" me too much. I have discovered that if you switch to SQL view and never go back to QBE view for that particular querydef, Access won't reformat your SQL and make it nasty so storing the complex string is just a safety play.
6. And finally, how much time are you actually saving? You can control the selection criteria in subforms by using a Where Clause that references the main form. All this process saves you in time is the overhead of Jet/ACE using ODBC on your query before it sends it off to the server. If you haven't done anything that can't be converted directly to T-SQL, Access just passes through your query so essentially all queries are pass through queries with a little bit of overhead if you understand what causes Access to get in the middle of things and request entire tables rather than just sending your query with where clause and waiting for the selected records to be returned.
Before you commit to redoing all your recordsource queries, I would do some time testing. If you can't discern a real improvement, then why bother? I learned a long time ago, if I'm going to use Access as an FE, it is better to just do things the "Access" way rather than try to outsmart it. It is smarter than I and it always wins in the long run.
