[AccessD] Option Groups in continuous forms break SQL Execute: (MULTI-ROW FETCH)?

[Jim Dettman]

Ryan,

 I was speaking more to Phillip's article.

 Many like to jump up and down about "bugs", but thing is, there's a lot going on, and not everything is as simple as it seems. 

 Part of the problem here is that database engine is not overly sophisticated (compared to something like SQL Server) and as a result, the query engine can make a very dumb choice on how it should go about things.

 I've seen cases where a query execution would go from something like a second or two to over a minute, just based on whether a table was full or empty at the time the costing was done.   Add on the fact that Access connects to so many things, yet still allows for things like VBA expressions, references to forms, etc, and has JET/ACE specific SQL, and that only complicates things.   Using any of those things, or a query with a local join means ACE/JET needs to process data locally, and when making fetches to a remote source, might need to fall into row by row processing at times.

  Running DELETE's against an ODBC data source will often cause this just based on the criteria applied.   That was documented in a MSKB article on this at one point.  

  So things like this are really nothing new, and it's entirely possible that changing an option value causes the query to be executed differently.  It may indeed turn out to be a bug (code already written not working to spec), but I have a sneaky suspicion this will be one of those that falls into the "by design" category.  Plus we are dealing with the fact that binding to a record source at runtime was tacked on, and as you know, things are never as clean as you'd like when that happens.

   But let's see what Shane comes up with.

Jim.   

 



-----Original Message-----
From: AccessD On Behalf Of Ryan W
Sent: Saturday, February 19, 2022 4:31 PM
To: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Cc: Jim Dettman <jimdettman at verizon.net>
Subject: Re: [AccessD] Option Groups in continuous forms break SQL Execute: (MULTI-ROW FETCH)?

I suppose you’re right but it’s not like an option group is doing anything but applying a check or radio button based on the option value, so why would this control alone cause efficient multirow fetching to vanish? Enumerating a columns data doesn’t seem like a good enough reason. 







Sent from my iPhone

> On Feb 19, 2022, at 2:53 PM, Jim Dettman via AccessD <accessd at databaseadvisors.com> wrote:
> 
> 
> I don't know that I'd call this a bug right off.
> 
> It just may be the nature of the beast.  There are a number of conditions
> that cause single row fetches rather than multi-row, and that's been true
> since Access 2.0 
> 
> This may be another one of those situations where it occurs and the reason
> why it does is just not obvious right off.
> 
> Jim.
> 
> 
> -----Original Message-----
> From: AccessD On Behalf Of Ryan W
> Sent: Friday, February 18, 2022 11:27 AM
> To: Access Developers discussion and problem solving
> <accessd at databaseadvisors.com>
> Subject: [AccessD] Option Groups in continuous forms break SQL Execute:
> (MULTI-ROW FETCH)?
> 
> Using an option group on a continuous form seems to use GOTO BOOKMARK when
> opening/navigating instead of MULTI-ROW fetch.
> 
> You need to turn on Access SQL Tracing to really catch this but you can
> also use SQL Profiler to see it's not preparing one statement for 10 rows
> at a time, but preparing 10 separate statements:
> 
> This post is semi-related to early/late binding the rowsource/recordsource
> bug here:.
> 
> https://codekabinett.com/rdumps.php?Lang=2&targetDoc=access-odbc-recordsourc
> e-disables-multi-row-fetch-continuous-form
> 
> Similarly if you early bind the option group controlsource you will get
> GOTO BOOKMARK lookups instead of one nice multi-row fetch. (late binding
> fixes this, UNTIL the below occurs)
> 
> This then compounds itself when you use the continuous form in a subform
> with a linkmaster/linkchild field relationship.. it completely breaks the
> multi-row fetch even if you are late binding the option group control.
> 
> 
> Shane if you see this can you get a confirmation on this?
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
-- 
AccessD mailing list
AccessD at databaseadvisors.com
https://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com