Data filtering when loading subform

[Guest]

My apologies if this is a double post. I lost my connection just as I posted it

I have a subform contained on a main form. The main form is bound to a query that I modify at runtime based on selection criteria. The subform is bound to a query that when linked to the main form displays a limited amount of rows. The subform is linked to the main form using a standard master/child link. The query for the subform will return several hundred thousand rows if executed outside of the form/subform relationship. My question is, since subforms always load before main forms, does this huge amount of data come across the wire and then filtered back to the few rows I want displayed? Or does Access only bring across the wire the data that will be displayed based upon the results of the query on the main form (that is not loaded yet)?
BTW: I have a FE/BE database with user level security applied. After modifying the sql of the query for the main form, I set the main form's recordsource property to the query that I just modifed.

 

[Van T. Dinh]

I don't think so as I haven't observed long delays in the Form/Subform
combinations I used. One of the Form being used as SourceObject has over >
200K rows when opened on its own.

OTOH, I believe that even when a Form in CtsFormView has many Records / rows
in the RecordSource, Access only retrieves enough rows (or slight more than
enough + indices???) to fill the Form's visible window.

--
HTH
Van T. Dinh
MVP (Access)

My apologies if this is a double post. I lost my connection just as I posted it.

I have a subform contained on a main form. The main form is bound to a

query that I modify at runtime based on selection criteria. The subform is
bound to a query that when linked to the main form displays a limited amount
of rows. The subform is linked to the main form using a standard
master/child link. The query for the subform will return several hundred
thousand rows if executed outside of the form/subform relationship. My
question is, since subforms always load before main forms, does this huge
amount of data come across the wire and then filtered back to the few rows I
want displayed? Or does Access only bring across the wire the data that will
be displayed based upon the results of the query on the main form (that is
not loaded yet)?

BTW: I have a FE/BE database with user level security applied. After

modifying the sql of the query for the main form, I set the main form's
recordsource property to the query that I just modifed.

 

[Guest]

I was really curious on this one so I did an experiment. I linked the main form query into the query for my subform. Since I modified the first query programatically and the first query is linked into the subform query, I had to reset both the main form and subform datasource again

Ex. set mainform.datasource = "MainFormTemp
set subform.datasource = "QueryLinkedWithMainForm

I then had my network people run a sniff. The classic form/subform scenario passed 24.5 meg across the wire after searching and retrieving a record
The modified subform query that had the main form linked to it and then reset the datasource passed 2.2 meg across the wire for searching and retrieving the same record
Although the actual speed was not that different, the difference in the amount of data passed was significant. I think that the reason that the speed was about the same was that it takes several seconds to reset the datasource. Would setting the query to a recordset and then setting form's recordset property by faster

Is there anyone out there that has had similar experiences

Thanks

Fre

----- Van T. Dinh wrote: ----

I don't think so as I haven't observed long delays in the Form/Subfor
combinations I used. One of the Form being used as SourceObject has over
200K rows when opened on its own

OTOH, I believe that even when a Form in CtsFormView has many Records / row
in the RecordSource, Access only retrieves enough rows (or slight more tha
enough + indices???) to fill the Form's visible window

--
HT
Van T. Din
MVP (Access

My apologies if this is a double post. I lost my connection just as posted it

query that I modify at runtime based on selection criteria. The subform i
bound to a query that when linked to the main form displays a limited amoun
of rows. The subform is linked to the main form using a standar
master/child link. The query for the subform will return several hundre
thousand rows if executed outside of the form/subform relationship. M
question is, since subforms always load before main forms, does this hug
amount of data come across the wire and then filtered back to the few rows
want displayed? Or does Access only bring across the wire the data that wil
be displayed based upon the results of the query on the main form (that i
not loaded yet)

BTW: I have a FE/BE database with user level security applied. Afte

modifying the sql of the query for the main form, I set the main form'
recordsource property to the query that I just modifed

 

[Van T. Dinh]

Aren't you using the LinkMasterFields / LinkChildFields?

Is the ForeignKey in the Child Table indexed?

--
HTH
Van T. Dinh
MVP (Access)

I was really curious on this one so I did an experiment. I linked

the main form query into the query for my subform. Since I modified the
first query programatically and the first query is linked into the subform
query, I had to reset both the main form and subform datasource again.

Ex. set mainform.datasource = "MainFormTemp"
set subform.datasource = "QueryLinkedWithMainForm"

I then had my network people run a sniff. The classic form/subform

scenario passed 24.5 meg across the wire after searching and retrieving a
record.

The modified subform query that had the main form linked to it and then

reset the datasource passed 2.2 meg across the wire for searching and
retrieving the same record.

Although the actual speed was not that different, the difference in the

amount of data passed was significant. I think that the reason that the
speed was about the same was that it takes several seconds to reset the
datasource. Would setting the query to a recordset and then setting form's
recordset property by faster?