Easily Translate Query SQL to Code Readable SQL
I never fully learned the multiplication tables in grade school.
I was way too hyper as a kid. Nowadays, they probably would have given me Ritalin. Maybe I need it now... Amazingly, I became a mechanical engineer with a specialty in mathematical modeling.
I memorized a few guideposts such as 6*8=48, so if you asked me what was 6*7, I'd do the arithmetic in my head that it was 48-6= da da: 42!
Worse, for an Access developer, I was trained as a mechanical engineer meaning I had no formal training in SQL. So, for years, I got by using the query grid, which I did master, and saved the queries as objects. One of my core applications ended up with 3,600 action queries. But, early on I failed to normalize everything, and that ended up biting me.
My solution was to create SQL in code, and cycle through in loops, just changing variables in code. But, I only was able to do it when I discovered Allen Browne's useful form that allowed copying the SQL from a query into the form and then have it's code "convert" the SQL to code readable form. But Allen "retired" from Access development years ago, so we can never be sure when his site will go down. I made the form and dolled it up a bit by automatically adding the DoCmd.RunSQL "strSQL" statement at the end. If I have to change variable(s) in a code loop, I put a dummy values in the query (e.g. 1 for a long integer in criteria) and sometimes tricky things (hey, I have to get paid somehow). Download my sample from here.
To use this marvelous little toy:
Download the zip, unblock and extract the Access Database ConvertSQL.accdbImport the form aaaConvertSQL into your applicationCreate an action query (append, delete, update) and switch the view to SQL, then copy the statement (select, then control + C).Open form aaaConvertSQL - the reason I name it this way is I can remember to search on aaa and find it.Paste the SQL statement into the upper text boxClick the Build SQL buttonCopy the code-readable statement out of the lower text box.
Then Paste this into your code.
And, don't forget to Dimension a string 'strSQL' in your code module.
This single thing has saved me more time than you can imagine. Even if I knew SQL well, the syntax used for complex queries would cost a lot of time. Also, remember that while a query can resolve a control's content, code-readable cannot. So, instead of using the builder to refer to the control in the query, just put in a constant. That way in the code, you can dimension a variable, capture the control's content, and then replace the constant in the SQL with the variable. There's a whole technique I've developed to do that efficiently. Send me a note if you want help and I'll try to post the answer here.
Personally, in most cases, I prefer action queries rather than code-readable SQL. But, and here's a tip, by default, action queries have their USE Transaction property set to Yes. Unless you need that, it just slows down the system and bloats the file size since it has to create scratch files every time the query is run. Better to set that property to NO.
Unfortunately, every time you create a new query, you have to set the property to NO. Since I have thousands of queries, I've developed code to cycle through the queries, determine if the query is an action query and then set its Use Transaction property to NO. I wish the default was No, with an option to change the default - are you listening Microsoft? If you want a sample of that code, let me know and I'll post it here.