DoCmd.OpenForm Where clause efficiency 1

[Tekime]

Hi all,

I am hoping somebody could attempt to clarify something for me

I am trying to open a form with a specific recordset using a command button. I can accomplish this with DoCmd.OpenForm and specify a Where clause for the record source.

Two things are bugging me though. First, records still come up as 'filtered' in the form, and the 'filter' attribute of the form has the Where clause displayed in design view. Does this mean that all of the records in the form's table were selected but subsequently filtered? Or just the indexes of the entire table and the data of the matching records? Or just the data of the matching records?

Second, the acLast attribute still reflects the highest record number in the form's associated table. This contributes to my thinking that OpenForm might be fetching every record in the table.

Sorry if this is an obvious question, I've dug through the Access 2000 Complete Reference and Google'd for answers without much luck. Thanks

http://www.fury-tech.com

 

[RickSpr]

Excellent question! I had always assumed that when you filter a form's recordset (whether by OpenForm, using the toolbar, or setting the Filter property from within the form--it's all the same) Access built a new SQL statement and requeried the form. But your observation that the navigation bar displays the total number of records, including the unfiltered ones, made me think this out, and my assumption was wrong.

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.

Note that if you have built in an Order By clause in the query that forces Access to perform a sort, it has to read all the rows and perform the sort before it knows which record is first. That could take a while. In that case it fills in the total record count first. But if your Order By matches an index, Access will use that index so it doesn't have to sort and the first records are displayed right away.

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.

I wasn't sure what you meant by the question about acLast. Are you talking about using it in a GoToRecord call after you open the form? If so, yes, it'll have to wait until the recordset is fully built--and possibly sorted--before the last record can be displayed. Remember, though, that if you are using a dynaset and have an index on the Order By columns, this will be faster than having to read the entire table.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Tekime]

Sorry for the late reply, I've been quite busy with this project. Again you have more than answered my question, thank you!

When I mentioned acLast, I was playing around with displaying the total number of records currently being displayed at the top of the form. It kept displaying the highest record number in the table, whereas the navigation buttons reflected the proper number of records based on the query or filter the recordset was based on.

Right now I am using a Dynaset and I have indexed all of the order by columns. I have indexed my foreign keys and any keys used as a clause in the most common queries. The load times aren't half bad, but I'll find out for sure when we dump a few hundred thousand records into my Orders table

Thanks again Rick, your knowledge is absolutely invaluable! is there a limit to how many stars we give?

http://www.fury-tech.com

 

[RickSpr]

Well, now that I've come back and looked at what I wrote, I'm wondering where my brain was. The navigation bar doesn't display the total number of records, including filtered-out ones, when a filter is active. I must have been half asleep when I wrote that. It was nice of you not to point out my mistake.

Using VBA code, you can get the same number as the navigation bar displays, as follows:

    Dim rst As DAO.Recordset
    Set rst = Me.RecordsetClone
    rst.MoveLast
    txtRecordCounter = rst.RecordCount
    rst.Close
    Set rst = Nothing

Stars limit: One per nominee, per thread. Thank you for the star in the other thread.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[Tekime]

Hehe, no problem Rick. Do you still believe that Access will open the form's default recordset and then filter those results when opened with DoCmd.OpenForm and a clause? I don't see why it would say 'Filtered' next to the record nav. if it did not. Either way, your clarification of the RecordSetType(s) shows me it probably isn't a big difference either way.

Thanks for the bit of code, I figured I would need to open a recordset to get the last record in the first place. But when I used acLast without defining a recordset object first it worked fine (until I filtered my query).

http://www.fury-tech.com

 

[RickSpr]

Yes, I still believe the base recordset is opened first, and then a filtered recordset is opened from the base. My reason for believing this is, every time I have ever had a large recordset and filtered it, the instant I turn off the filter, the full recordset is back again. If Access actually built a new SQL query statement and ran a brand new query every time you turned filters on and off, then turning off a filter would incur almost as much overhead as opening the form in the first place. But turning off a filter always seems to me to be fast, so I assume the unfiltered recordset was still open.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein