Access Project: Form bound to ADODB.Recordset does not view all records when both Filter and Sort are applied

Form of an access project (Access 2003) connected to SQL Server 2000.
Not using the RecordSource property, but rather setting the Recordset property of a form to an opened ADO Recordset.
All works well, EXCEPT FOR when setting BOTH the FILTER and SORT properties of the recordset, in which case only the first 100 records are displayed. And no, this has nothing to do with the MaxRecords property...

How to reproduce:
1. Create a new, blank continuous form.
2. Copy the text below to the form's VBA module.
3. In the module, search for the word REPLACE and make the obvious replacements (name of database, source object, filter, sort expression)

Thanks for taking the time for looking at this!

'THIS IS THE MODULE OF A TEST FORM
'The RecordSource property should be left BLANK

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.AccessConnection
'REPLACE qry_ProposalHeaders with the
'name of a table/query that has more than 100 rows.
.CommandText = "tbl_ProposalHeaders"
.CommandType = adCmdTable
.Prepared = True
End With

Have you tried using a standard SELECT statement instead of directly opening a table? There are many restrictions when using table-type recordsets in ODBC connections.

Set cmd = New ADODB.Command
With cmd
Set .ActiveConnection = CurrentProject.AccessConnection
'REPLACE qry_ProposalHeaders with the
'name of a table/query that has more than 100 rows.
.CommandText = "SELECT * FROM tbl_ProposalHeaders"
.CommandType = adCmdText
.Prepared = True
End With

The recordset itself does show all records -- the problem lies in the Access form, specifically in the fact that it displays only the first 100 records of the Recordset.

To remove all doubt, I tried (already prior to posting the question) opening the recordset both with and without the CMD object, and also while using either sql text (as appears in the above code) or specifically the name of a query or a table, in which case I indicated adCmdTable as the CommandType. Yet, to no avail...

If you navigate to the last record (i.e. record #100) does the recordset refetch more records?

Have you verified that the recordset contains only 100 records AFTER you set that recordset to the form.

For the record: I have advised you that you must set the cursor to clientside; that's not technically true. Depending on the Provider you use for your connection, you can set this to a serverside cursor, and I'm wondering if that would solve your troubles. See this article:

have tried all that... the Recordset has the right number of records, the form's recordset then takes only the first 100 records. No navigation on the screen changes the situation or allows it to take the desired direction. Same goes for using client-side/server-side cursors...

I believe this behavior is by design, but there must be some way of controlling it! Or is it really that MS doesn't really want you to have full control over your Recordsets and use it only as a tool for displaying and editing the data?

Built a new ADP project, connected to an existing data source (SQL Server 2000). Built a simple form, bound to a table, added all fields. Set view to DAtasheet.

In the Open event, built an ADODB.Recordset that pulled all records from my table (306 records). Set my form's recordset to that new ADODB recordset. Delete the Form's RecordSource but left my .ControlSources in place on the textboxes and such.

I see ... I can issue a SORT or a FILTER and I get all records ... if I enter BOTH, I get 100 records. I'm not sure why this occurs ... I know that sorting a recordset in this fashion forces ADO to cache the records locally, build index tables (if needed) and then sort ... perhaps this has something to do with it.

FWIW, I've never used the .Sort of .Filter property of a recordset, nor do I know of an professional developers who would use this. MS suggests that for production applications that you use those instead of the Recordset properties as well.

IOW ... open your recordset like this:

rst.Open "SELECT * FROM tProjectMilestones WHERE sAR='F' ORDER BY lProjMileStoneID", YourConnection

This resulted in the expected recordcount (280, in my case) and is was ordered correctly.

You are right about the performance aspects on one condition: you know in advance what the sort would be. In my case, the recordset is initially opened with no filter/sort at all, and then the user chooses to filter and/or sort it. Using the .Filter and .Sort properties would save the extra execution of the query, since the client already has all records.

The above means that filtering the recordset using the .Filter property is actually much faster and saves server resources, that performing a subsequent execution of the query with the applicable WHERE clause.

Given the behavior of MsAccess, I now created a mechanism that either applies the filter locally (if no post-execution sort was chosen), or embeds it into the TSql statement (when the user chooses a sort that is different than the default one).

JUST THINK ABOUT IT: when you open an Access form, and then clicks the "Sort Ascending" button, there should actually be no reason for the server to re-execute the query - the client already has all data, and could sort it locally. That's what the recordset's .Sort property is for.

<when you open an Access form, and then clicks the "Sort Ascending" button, there should actually be no reason for the server to re-execute the query - the client already has all data, and could sort it locally. That's what the recordset's .Sort property is for.>

I'd agree, but it appears that's not going to happen, at least with the settings you're using. You might try a different locking scheme ...

Remember too that in an Access FE, the workstation handles all processing, regardless of the type of database you're hitting (unless you've impelemented Views or Stored Procs, that is). You'll ALWAYS make a roundtrip to the "server", even if you're using a server-type database.

Also, in Access it's recommended that you use the Form's Sort and Filter properties instead of working with the underlying recordset directly.

Given my overall setting I need to do all the processing on the recordset/vba level, and then "hand" the form an already-established recordset object. The problem is that one has to choose: once setting the form's Recordset property, the built-in filtering mechanism no longer properly works, which makes it necessary to directly set the recordset's filter property. And this - as you've seen - makes renders the built-in sort mechanism useless (since it ignores the filter that was just applied...)

As there appears to be no setting-based solution of the original problem, I am now using a filter/sort wrapper which automatically decides whether to filter the already-retrieved records or simply re-run the query while using an extended WHERE clause. SORTING is now always part of the TSql statement (no longer using the recordset's .Sort property), so setting the form's Recordset property no longer generates the original problem.

In any case, I appreciate your efforts and sharing of perspectives and experience, and would hence accept your above comment as a solution to this question.

I have more info on this problem. I tried saving the filter in a string, setting it as empty, running the sort, and then resetting the filter. It seems to work. I've solved a lot of weird ADO ACCESS ADP problems by just trying many different combinations of code. G*d only knows how it all works... certainly noone at Microsoft does !!

i had exactly the same problem as described here, and have been struggling with it for quite some days. online searches didn't help either, not many articles about this problem.

in my situation Access 2003 or 2007, doesn't matter, access database with forms acting as a FrontEnd and connecting to another access 'data' database with ADO.
when the underlying ADO recordset had a filter and a sort and a form's recordset was set to that recordset, only 100 records showed up... :-(

unfortunately pcamina's solution didn't work for me, it results in a form showing an sorted but unfiltered recordset.

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…

Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…