When using an Access FE with an Access BE, I often use filtering,
docme.openform with a where parameter specifying the specific record to
be opened. I tend to do this after I have presented a list of records
matching selection criteria entered.

I found this comment at the location specified:

I now believe that the form always opens its underlying recordset, the
one defined in its RecordSource property. Whether it reads all the rows,
or just the indexes and enough rows to fill the form, depends on the
form's RecordsetType property. The default setting, "Dynaset", builds a
list of pointers to the rows but doesn't actually read any more rows
than necessary. If you change the setting to "Snapshot", it reads all
the rows right at the start. In either case, however, it displays the
first rows as soon as they're identified, which is why sometimes you can
see the form data before the total record count on the navigation bar is
displayed.

.. . . . .

When the filter is applied--whether as part of the Open process or
later--I believe Access opens another recordset based on the
already-open recordset (using the Recordset.OpenRecordset method). I
expect it always has to wait until the first recordset has been fully
opened before it can start opening the filtered recordset, so in this
case you'd have to wait until the underlying recordset (the RecordSource
recordset) had been fully built. But again, if it's a dynaset, you're
only processing indexes so it's pretty fast.

.. . . .
Rick Sprague

http://www.tek-tips.com/viewthread.c...76472&page=770

What Rick says seems to make sense as if I open my form using my usual
technique, but then click on the button to remove the filter, I
IMMEDIATLY see the first record of the entire recordset - no delay.

What I don''t understand is how much of what data is read before the
filter is applied? Indices and if so which? How many records, enough
for the first page, just as would be done if no filter and then use the
filter to process further?

My question then really deals with whether using a datasource as a query
with a WHERE source would make much difference. I think I say a posting
by Albert Kallal which said the difference would not be noticable unless
you had a large number of records in the original datasource. My
current project has about 18k records and they are fairly large.

Anyone here with experience in looking into this?

thanks

bob

BobAlston <bobalston9 (AT) yahoo (DOT) com> wrote in
news:iul5mi$dpj$1 (AT) dont-email (DOT) me:

I'm not sure I'm convinced by Rick's speculations.

I would say there's a difference between when the filtering happens
in these two cases:

1. a filter defined and saved in the form's .Filter property, such
that it is always applied each time the form opens.

2. a WhereCondition argument of the DoCmd.OpenForm command.

In my experience, the latter happens before the form is opened
(i.e., the filtered records are all that is ever requested), while
the former situation does load the whole form and then the filter is
applied after everything has been loaded.

Because of this, I generally do not use saved filters at all.

The other thing that this makes me think of is when you've got an
ORDER BY defined in the form's OrderBy property -- the same thing
happens. But also, if your form's underlying recordset has an ORDER
BY defined, it can have an effect on how many records are retrieved
(but it depends on what you're sorting on, of course).

My experience is that the form with a filter property is much less
efficient than the WhereCondition in the DoCmd.OpenForm command.

I would say that you could be as efficient as possible by having
your form have an empty recordsource using the smallest table local
to your front end:

SELECT TOP 1 Null As Field1, Null As Field2, 0 As Field 3
FROM MSysAccessObjects

(I chose MSysObjects because in a virgin database, it has at least 1
record, and of the tables that are guaranteed to have at least one
record, it has the smallest number)

This allows you to have bound controls and not have #Name errors --
it will just show one uneditable blank record. Then you could change
the Recordsource to display any subset of records you like.

In an app of mine that for years used an MDB as the back end (years
after it should have been upsized to SQL Server), I used that
approach, and it worked extremely well, even for the main form,
which loaded data from a table with 250K records in it (and 350K and
600K records in its child tables, i.e., displayed in subforms).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/