VBA SQL vs "GUI"

[raziel3]

Hi everyone. Just wanted to get an idea of everyone's development style.

Action Queries, do you all (the more seasoned developers) prefer to type out the SQL in VBA or create the query GUI and refer to it and why?

 

[The_Doc_Man]

Not limiting myself to action queries for my answer.

For well over half of all the queries you will write and store to become a named query, the query (GUI) grid will be all you need. Most queries are just filtered SELECT queries that you can use as the .RowSource of something or the .Recordsource of something. Action queries are also commonly simple enough for you to start with the GUI. However, you are asking a question as though it is an EXCLUSIVE OR - GUI or VBA - when in fact, you are taking a narrow viewpoint.

I usually start from the query grid (even for action queries) and then tailor it by editing the SQL - usually to get rid of all those excessive frickin' parentheses that the GUI query grid inflicts on us. But seriously, all of the methods you named should merely be tools in the toolkit and should each be used when appropriate and possible.

I often use the query grid to make an SQL string by dragging and dropping fields - so that I know there will be no spelling or location errors. But if there is some dynamic aspect, I then cut/paste the SQL string into pieces-parts so that I can insert "extras" using VBA to manipulate the strings. The idea is, use the GUI any time you have a lot of potential typing to do so that you can start with something that has already been implicitly vetted to be syntactically and referentially correct, then go full speed ahead from there.

 

[MajP]

I think you will find mixed opinions. Some people go to the extreme of putting all in VBA feeling it is easier to manage where others feel it is easier to manage a lot of stored queries. I tend to be in the middle using both whatever is quicker and easier.

 

[MajP]

But I think your question is about creating dynamic query in VBA vs storing a query and then calling it. Regardless if that query was created in the QBE or SQL view.

If the question is QBE vs typing SQL in the query editor see this

SQL Editor Add-In Recommendation

Do you use any third party Add-in or application for replacing/enhancing the SQL editor? This has been a long time complaint. Doing just a Web search I see there is quite a bit out there. Anyone have experience or recommendations? Anyone built one? Could be a good group project. Also a year...

www.access-programmers.co.uk

There is some discussion here on speed and efficiency
Thread 'On fly (VBA) Form's record source' https://www.access-programmers.co.uk/forums/threads/on-fly-vba-forms-record-source.318829/

 

[Minty]

I do both, unless I'm using SQL server and SSMS, then I tend to write queries directly into the editor, as it has IntelliSense, and you can format them how you want and include comments if needed.

 

[plog]

Action Queries, do you all (the more seasoned developers) prefer to type out the SQL in VBA or create the query GUI and refer to it and why?

I'm trying to read your true intent with that question and it's kind of ambiguous.

1. Are you asking how/where to write your SQL? Any query your write, even one that is going to live in VBA should first be tested as a query object in Access. The GUI makes it easy to whip out the SQL and even if you just want to write SQL directly into the SQL view it allows you to test your query to make sure it's syntactically and logically correct.

2. Are you asking where should an Action query exist--as an object or as a line of VBA code? If it is a static query, I suggest you build a query object and reference that from VBA. That way it is both easier to edit that query and that query is available to your entire database and not just that block of code--it will be able to run that exact query from anywhere without copying your VBA code all over the place.. If it is a dynamic query--one that you want to use VBA variables in or conditionally add parts to, then it should probably live just as code in VBA.

 

[ebs17]

ACTION Queries ... don't happen very often at all, apart from the beginner's problem that because of the lack of ability to build proper functional select queries, there is a lot of back and forth in real and temporary tables as a substitute.

I tend to have action queries as part of larger processes and transactions. A transaction is a VBA process, and I tend to have the SQL statements directly in the code, both for clarity and to keep the process together as a unit. But that's not a law.

It also depends on whether the query is used statically or whether parts of it would have to be varied dynamically beyond parameters.
A static query with a length of 400 characters, for example, is very difficult to read in VBA and also destroys the readability of other VBA code. It would be better to call up an understandable query name.

So: It depends. Query is not the same as a query.

 

[raziel3]

I'm trying to read your true intent with that question and it's kind of ambiguous.

Sorry about that. Point 2 answered the question I should have asked initially. "Where should the Action Query exist?"

 

[Pat Hartman]

I've been using SQL for more than 40 years. In the 80's and 90's it was embedded SQL in COBOL. I used to dream of a way to remove the burden of having to remember all the table and column names and manage to type them without error, especially when I wasn't the one who built the schema. Then in the early 90's, I discovered Access. I found that I could link to DB2 (IBM's RDBMS offering) on the mainframe and view and update with Access. I was hooked. AND I got a GUI SQL editor to boot. OK, it's not a good editor but for the vast majority of queries, it is good enough. I can point and click and not worry about typos. I can use criteria so the values can be provided at runtime. I save the queries as QueryDefs and I NEVER look at them in SQL view because the editor, changes the formatting to suit itself.

So, unless the SQL is actually dynamic, I use querydefs. There is a trick though. Sometimes I have complex selection criteria and the editor always wants to rearrange it and add a gazillion parentheses. However, I can foil it by switching to SQL view and creating the WHERE clause there. I can format it neatly (I don't bother with the Select clause) and save it. As long as I never switch to QBE view, the editor won't mess with the SQL string. As a back stop, I also keep a table where I can paste the string after I build it so if I lose my mind and switch to QBE view, I can just pull the string out of the table and be back in business.

Dynamic SQL usually only occurs when you have complicated search forms or you build a fancy, custom export process for the user. Otherwise, SQL is static and so QBE is the simplest solution. Remember, no one is going to make you look at the SQL string so you will only be offended by its lack of formatting if you look. Just don't look

One place I always avoid SQL strings is as the RecordSource for forms or reports. I always use Querydefs for those. WHY? Because there is a long standing bug in Access where Access loses the SQL "string" and assumes it to be the name of a querydef so it truncates the SQL. I just had it happen last week. I have no idea what triggers it.

 

[561414]

I use the query builder for SELECT queries. If I need to review the SQL string and it's too long or complex, I use this site

SQL Formatter & Beautifier Online

Best and Free online SQL Formatter tool, SQL Beautifier for SQL Server, Oracle, DB2, MySQL, MariaDB, Sybase, Access and MDX. It uses SQL Parser to Pretty Print SQL

codebeautify.org

Then I remove parentheses to copy the output of the site and then I paste it back into the sql editor of the access query builder, then I test my SELECT query and then I store it as a query definition because I usually need to refer to it from VBA to filter it dynamically later. For action queries, I just type them in the VBA editor and I'm done.

However, it's 2023, so I use a simple VBA function that returns something like this: table:field1,field2...
Then I just use ChatGPT or Github Copilot prompting the thing I wanna do and it conveniently does it for me.