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

Mon Feb 21 08:15:33 CST 2022

Shane (and others)

I've made a little accdb with 4 forms.

All of these require you to turn on TraceSQLMode in the registry and watch
the sqlout.txt

One is an early bound recordsource form which does multi-row fetch right
from the get go.
Second is a late bound recordsource (On Form_Open) form that does GOTO
BOOKMARK from the get go as illustrated here:
https://codekabinett.com/rdumps.php?Lang=2&targetDoc=access-odbc-recordsource-disables-multi-row-fetch-continuous-form
Third is an early bound recordsource form with an OptionGroup that is early
bound to the ID field. Which does GOTO BOOKMARK upon opening, but as you
scroll it does multi-row fetching.
Fourth is an early bound recordsource form with an OptionGroup that is LATE
bound on the Form_Open event that does multi-row fetching right from the
get go.

The data is hosted up on a private server I have with a read only database
user.

https://drive.google.com/file/d/1dS9rRBserRFRKp8Sp530y_oj78wHLaoZ/view?usp=sharing

Please share your thoughts.

On Sat, Feb 19, 2022 at 11:29 PM Shane Groff via AccessD <
accessd at databaseadvisors.com> wrote:

> Hi Ryan,
>
> If you want to share a database with a 'good' form, and a 'bad' form (if
> you can also make a backup of your SQL database, without data, that would
> be helpful too), we can take a look.
> (I'll try to set something up if you can't do this, but it will take
> longer to get to it)
>
> Shane
>
>
> -----Original Message-----
> From: AccessD <accessd-bounces+shaneg=microsoft.com at databaseadvisors.com>
> On Behalf Of Ryan W
> Sent: Saturday, February 19, 2022 1:35 PM
> To: Access Developers discussion and problem solving <
> accessd at databaseadvisors.com>
> Cc: Jim Dettman <jimdettman at verizon.net>
> Subject: [EXTERNAL] Re: [AccessD] Option Groups in continuous forms break
> SQL Execute: (MULTI-ROW FETCH)?
>
> Hit send too soon.
>
> If unset the option groups control source , set the subform recordsource
> and then set the option group controlsource again the traces are a lot less
> chatty as well.  I’m using this continuous form on a tab control and
> changing the data between 4 sets of “grouped records” dynamically.
>
> Doing the above gets rid of most the chatter switching tabs except the
> initial load or switching the index to an entirely different PK.
>
>
>
> Sent from my iPhone
>
> > On Feb 19, 2022, at 3:31 PM, Ryan W <wrwehler at gmail.com> wrote:
> >
> > 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://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fcod
> >> ekabinett.com%2Frdumps.php%3FLang%3D2%26targetDoc%3Daccess-odbc-recor
> >> dsourc&data=04%7C01%7Cshaneg%40microsoft.com%7C4390581acae549cf72
> >> ea08d9f3efab6e%7C72f988bf86f141af91ab2d7cd011db47%7C0%7C0%7C637809032
> >> 978623056%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzI
> >> iLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=nMmg64rZIEEy67JnmZT1
> >> tGUrOBdjZikcwtZGhc%2BN6G4%3D&reserved=0
> >> 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://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdat
> >> abaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cs
> >> haneg%40microsoft.com%7C4390581acae549cf72ea08d9f3efab6e%7C72f988bf86
> >> f141af91ab2d7cd011db47%7C0%7C0%7C637809032978623056%7CUnknown%7CTWFpb
> >> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> 0%3D%7C3000&sdata=4xII3bVjV8hVCANje7k2yf0VXdsdVL4%2BIRLdz4fkOqE%3
> >> D&reserved=0
> >> Website:
> >> https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.
> >> databaseadvisors.com%2F&data=04%7C01%7Cshaneg%40microsoft.com%7C4
> >> 390581acae549cf72ea08d9f3efab6e%7C72f988bf86f141af91ab2d7cd011db47%7C
> >> 0%7C0%7C637809032978623056%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMD
> >> AiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=A7f
> >> oRtol4OvMSbdDWKjGrUl0MQu44w0ZwjRh8IF%2B1F0%3D&reserved=0
> >>
> >> --
> >> AccessD mailing list
> >> AccessD at databaseadvisors.com
> >> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdat
> >> abaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cs
> >> haneg%40microsoft.com%7C4390581acae549cf72ea08d9f3efab6e%7C72f988bf86
> >> f141af91ab2d7cd011db47%7C0%7C0%7C637809032978623056%7CUnknown%7CTWFpb
> >> GZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn
> >> 0%3D%7C3000&sdata=4xII3bVjV8hVCANje7k2yf0VXdsdVL4%2BIRLdz4fkOqE%3
> >> D&reserved=0
> >> Website:
> >> https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.
> >> databaseadvisors.com%2F&data=04%7C01%7Cshaneg%40microsoft.com%7C4
> >> 390581acae549cf72ea08d9f3efab6e%7C72f988bf86f141af91ab2d7cd011db47%7C
> >> 0%7C0%7C637809032978623056%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMD
> >> AiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=A7f
> >> oRtol4OvMSbdDWKjGrUl0MQu44w0ZwjRh8IF%2B1F0%3D&reserved=0
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
>
> https://nam06.safelinks.protection.outlook.com/?url=https%3A%2F%2Fdatabaseadvisors.com%2Fmailman%2Flistinfo%2Faccessd&data=04%7C01%7Cshaneg%40microsoft.com%7C4390581acae549cf72ea08d9f3efab6e%7C72f988bf86f141af91ab2d7cd011db47%7C0%7C0%7C637809032978623056%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=4xII3bVjV8hVCANje7k2yf0VXdsdVL4%2BIRLdz4fkOqE%3D&reserved=0
> Website:
> https://nam06.safelinks.protection.outlook.com/?url=http%3A%2F%2Fwww.databaseadvisors.com%2F&data=04%7C01%7Cshaneg%40microsoft.com%7C4390581acae549cf72ea08d9f3efab6e%7C72f988bf86f141af91ab2d7cd011db47%7C0%7C0%7C637809032978623056%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000&sdata=A7foRtol4OvMSbdDWKjGrUl0MQu44w0ZwjRh8IF%2B1F0%3D&reserved=0
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> https://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>