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?

 

[Pat Hartman]

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 haven't worked with one of my queries with the complex selection criteria using Monaco. I'll try to find one of the problem queries to see what happens. But, the QBE would format them so that it was logically easier for the QBE to display the OR's in the way they wanted them graphically, one on top of the other, but when you read the criteria of the SQL, the changes make it a real jumble with huge reptation especially if you didn't use Alias names for the tables/querydefs. And even with tiny alias' it is much more difficult to read.

True, but how often do developers actually need to check query/SQL documentation or dependencies during development work?

I actually use my documentation tool whenever I'm considering any type of large schema change so that I can estimate how long something will take based on the extent of the objects I'm going to need to change to do it. And I use it fairly frequently during development so I can make sure I am being consistent with definitions and object naming.

Since my work as a consultant requires me to occasionally modify someone else's app, I find being able to extract all the table usage information using the MSys object table very helpful and am always disappointed to find the programmer had used embedded SQL.

I agree, there are lots of little tweaks I would make to the QBE to make changes easier. Without seeing the code behind it, it's hard to say why certain decisions were made. I have the utmost respect for the initial MS Access developers. The fact that this tool is alive and well going on 40 years later is a testament to their foresight and the completeness of their work. Lots of changes have been made over the years. Some good, some bad, some fabulous but someone from the early 90's could absolutely use any version up until 2007 without a problem and probably be able to work with the .accdb versions easily enough. Things like prefixing everything with table names they probably do because there is no good point at which to fix the problem should the next table/query you add cause a naming conflict so that is probably again a practical decision that both you and I would have made had we written the QBE code. But again, unless you switch to SQL view, what does it matter? I only care about what the QBE does to mess with my criteria because I occasionally have to change it and I never want to have to deal with the mess they make of it. If I never had to ever change it, I wouldn't even see the mess because I would never look at the SQL.