Just one little Index

[Pat Hartman]

the QBE does have its limitations and annoyances such as excessive use of parentheses and inclusion of superfluous fields.

But if you use the QBE, you have no need to even look at the generated SQL. So, if you are looking at the SQL, is it because you are then taking the generated SQL and trying to embed it as text?

I'm pretty sure that all the excess parentheses are there to help with converting the string to the graphic view. If you create the string in text view and never save the query after switching to graphic view, ie only save in string view, Access doesn't mess with your string at all. I use this trick for a couple of queries with complex criteria. The problem of course is sometimes I forget so In the databases where I have complex queries which I want to keep as strings (this is mostly because of the way Access seriously messes with the criteria when it is complex rather than the excess parens which are merely annoying), I create a table and paste the raw string into the table. That way I have a backup if I mess up and let Access "help" me.

I'm pretty sure MS wouldn't consider not adding all the parens but now that they are using Monaco, I think they may have added a second storage area so they can save my string separately from their string so even this issue is probably gone.

I don't want to quibble about querydefs vs embedded SQL since it is largely a matter of preference. I was merely pointing out earlier one benefit I had never really thought about and that was documentation. If all your queries are embedded text, there is no easy way to produce a cross reference of what is used where but the task is trivial if you use querydefs since you can use queries to interrogate the MSys tables for the information. I think I've posted excerpts from my documentation tool. If you want the code but can't find it, just ask.

In the past it really made much more of a difference which choice you made but the the stats came down to querydefs were better for two reasons.
Generating an execution plan takes time and space. When you use the QBE, Access generates the execution plan the first time you run the query and saves it with the querydef. Therefore all future executions use the saved querydef. So technically the execution is faster. In reality, unless you are running the same query hundreds of times, you wouldn't ever feel slowness so this is unlikely to ever cause a real problem in most applications. However the second issue was space and this one has been cleared up so you no longer need to worry about it. Access isn't good at garbage collection. For most things, you actually have to run a C&R to get Access to discard all of its scratch pad area which it used to create the execution plan. So, this accumulated over time and caused huge bloating issues if you didn't regularly C&R.

So, technically, today the QBE is still more efficient due to the pre-compile, but the difference isn't enough to worry about in the vast majority of apps so VBA vs QBE is really a matter of preference unless you want documentation. In that case QBE wins again

 

[isladogs]

The QBE is indeed an excellent feature and I'm not against its use in any way.

But if you use the QBE, you have no need to even look at the generated SQL. So, if you are looking at the SQL, is it because you are then taking the generated SQL and trying to embed it as text?

Sometimes yes, but often also to see whether I can simplify the query design further. Streamlining is my opinion often easier to see in SQL view

If you create the string in text view and never save the query after switching to graphic view, ie only save in string view, Access doesn't mess with your string at all.

Correct. Access stores the query in its last saved view (design or SQL). Exporting a query as text clearly shows the difference between the two cases

I think they may have added a second storage area so they can save my string separately from their string so even this issue is probably gone.

Incorrect. Monaco formats & displays the SQL 'on the fly'. It doesn't store anything...which in many ways is a pity!
For example, it cannot store the query formatting / font size etc

I was merely pointing out earlier one benefit I had never really thought about and that was documentation.

True, but how often do developers actually need to check query/SQL documentation or dependencies during development work?
It is certainly true that module code including embedded SQL cannot be analyzed using the built-in documenter or dependency tool.

However, on the occasions I do need to analyze a database in any detail, I use one or more of the following depending on need:
V-Tools (Deep Search) or the Dependency Checker add-in or Philipp Stiefel's Find and Replace utility or my own Database Analyzer Pro app

I can see why Access adds excessive parentheses in the QBE to facilitate QEPs. Similarly, why it sometimes 'mangles' SQL to make it more efficient.

However, these should be no need for the QBE to prefix each field with the table name unless there is a potential conflict
Nor should it need to add superfluous fields as in e.g. delete queries.

Unfortunately, Access does not create QEPs for SQL only query types or for subqueries

Another annoyance -in complex queries with multiple tables I often use aliases. These are easy to follow in SQL view but it is much harder to retrieve the table name for each alias in the QBE. Right clicking doesn't help. Nor does clicking on table joins. Tooltips rarely if ever work.
The table names are not even visible in the property sheet.
The easiest way is often to click on the aliased table header and open it in datasheet view. Or we can of course interrogate the MSysQueries table.
Why is it so unnecessarily hard?