Using Dynamic SQL versus Parameter Query

[LPurvis]

Hi Pat.
I certainly agree with your opinion on the division of responsibilities of each method. I think the two points you officially list are a good summary of the prime situations for exemption that are mentioned over several posts here and indeed the many threads this has been discussed on other forums.

We must indeed always bear in mind what's going to be parsable - by Jet or an ODBC source (I'd personally only be using SQL Server in such a scenario - but it does of course speak to DB2, Oracle, or Sybase etc.)
It's the developer's responsibility to do so.

Though I personally don't see the on the fly execution plan as always a bad thing - and the slight bloat (certainly by today's standards) is, IMO, quite reasonable as far as file size creep and as you say - combatable with Compact on Close (which I'm not wild about in itself but should likely only be implemented by the FE as needed - i.e. the developer chooses a file size beyond which is unacceptable).

I also fully agree (as have said earlier) about the limitations of quickly manipulating SQL when it's obscured across multiple lines of a VBA procedure. That's one reason why I also like the SQL in tables approach as a better mid ground.

However, in regards to a Jet based solution, I personally don't perceive there to be any confusion between filters and the WhereCondition of the OpenForm and OpenReport methods.
But in fact feel quite comfortable that they are indeed both implementing filters. I'm not sure if I stated as much here or have only done so elsewhere - but it's all about timing.
The point I was looking to make was that filters aren't necessarily the same beast - depending upon when they're implemented.
With a static full table/query bound form sat open, if we apply a filter to that form then we're issuing a relatively inefficient request. (We already had the data there and more!) Applying the filter then sees Access issue a request from Jet for the new results (Jet won't necessarily fetch those from the database though).
Passing a WhereCondition allows Access to show off ;-)
As you mentioned, it doesn't load the form's source and then query again for the filtered set. It includes the incoming filter as part of it's request from the database.
Access is capable of even doing just that if you apply the filter in the Open event of the form (i.e. before it's loaded the recordsource data) - however I trust the timing of that less than the absolute nature of a WhereCondition.

When a form is opened with a WhereCondition - that condition is set as that form's filter property. You can fetch it as such.
I don't know about anyone else - but when I open a form with a WhereCondition - it displays the (Filtered) text in the Navigation bar.
When you remove the filter (having opened the form with a WhereCondition) all records in the underlying table/query are available.
It's a filter. But having been applied in a timely fashion for maximum efficiency.

I, over time, think the word "Filter" has come to be used in the context of "inefficient local pruning of already fetched data".
That certainly can be the case for the unwary.
But Access (and let's not be shy about this - it's Access that is doing the thinking here - not even Jet) is capable of using filters in a more efficient way - if we give it the chance.

As I've already said at fairly great length though...
I don't use filters very much at all. (Unless you're include Whereconditions - then it goes up a bit ;-)

Cheers!

 

[Banana]

I really should experiment a bit more with WhereCondition, but I honestly find it alarmingly too easy for us to pass off a bad query. Having actually looked at the logs from the MySQL, I recall my horrors when I discovered I wasn't just doing a full table scan, but twice for every time I navigated a record. It turns out that I had to be quite explicit with the source; initially I had a combobox functioning in very similar manner to a cascading combobox. I had the code in the necessary events to adjust the source, but the initial rowsource didn't have a WHERE clause and it does nails the table for full scan. Only by setting the rowsource explicitly before the form is even loaded, does away with that unwanted scan. There's probably one more thing, but it escapes me now. Still, the basic point is that it may not be always enough to just having a WHERE clause- you would have to make sure it's there in the start, before the form even gets loaded and is still there all to the end so Jet doesn't hammer the server for a full scan.

In a sense, we could say that Jet is both very intelligent and breathtakingly dumb in how it deals with ODBC sources, and it's the developer's responsibility to know exactly what queries is actually sent to the server as it may not the same one you made in QBE. Thus I've fallen in the habit of testing every queries I pass off to Jet, especially for ones used by any forms before I give it out to my end users compared to when Access was the BE, I just had to looked at the results to verify they were what I wanted.

This is because of that, I've come to strongly prefer parameter queries because I find them much more easier to use and maintain, even if the coding become a bit more complicated (but looking at it again, I probably could have created a UDF to save myself the usual steps of accessing and using a parameter query in VBA).

The only thing I can't really change is Jet's tendency to send AUTOCOMMIT=1 and AUTOCOMMIT=0 between every requests, and that drives me crazy.