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!

'The RecordSource property should be left BLANK

Option Compare Database
Option Explicit

Private cnn As ADODB.Connection
Private cmd As ADODB.Command
Private rst As ADODB.Recordset

Private Sub Form_Open(Cancel As Integer)
End Sub

Public Function SetSource()

   Set cnn = New ADODB.Connection
   'For the following <with cnn..end with>,
   'see also Microsoft's KB 281998
   With cnn
      .Provider = "Microsoft.Access.OLEDB.10.0"
      .Properties("Data Provider").Value = "SQLOLEDB"
      .Properties("Data Source").Value = "(local)"
      'REPLACE <COMPANY_DATABASE> with the name of your database
      .Properties("Initial Catalog").Value = "COMPANY_DATABASE"
      .Properties("Integrated Security").Value = "SSPI"
      .CursorLocation = adUseClient
   End With
   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
   Set rst = New ADODB.Recordset
   With rst
        .CursorLocation = adUseClient
        .CursorType = adOpenStatic
        .LockType = adLockOptimistic
        'REAPLCE the following FILTER with one that would return more than 100 rows.
        rst.Filter = "CustomerId = 1006"
        'REPLACE the following SORT expression with one that is valid for your source
        rst.Sort = "ProposalSystemId"
        .Open cmd
        If Not .EOF And Not .BOF Then
        End If
   End With
   Set Me.Recordset = rst
   'BUG: ONLY THE FIRST 100 rows are displayed (on two different machines)
   'UNFORTUNATELY, this has nothing to do with the MaxRecords Property.
End Function

Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<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.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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

I don't use Command objects, so make sure of the syntax first ...
zgilboaAuthor Commented:
Hi LSMConsulting, and and thanks for the comment.

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...

Looking forward to future comments,
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Wondering if the Continuous form aspect has anything to do with this ... can you temporarily change to Normal view and see what happens.
zgilboaAuthor Commented:
tried that too... wish it were that easy:)
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I close to running out of ideas ...

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:

Note the section titled Requirements for Microsoft Jet regarding the two different providers you can use.
zgilboaAuthor Commented:
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?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I'm not sure ... I just checked this:

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 open the form and my nav bar indicates I have 306 records.

I also tried this against a SQL Server 2005 with the same results.

Here's the code I used in the Open event:

Dim rst As ADODB.Recordset

Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient

rst.Open "sELECT * FROM tProjectMilestones", CurrentProject.Connection

Set Me.Recordset = rst
zgilboaAuthor Commented:
the above scenario works since you did not set the recordset's filter and sort properties.  right after the line

rst.Open "sELECT * FROM tProjectMilestones", CurrentProject.Connection

try to add the following statements:

rst.Sort = <YOUR SORT HERE>

and then you'll see the problem...
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
zgilboaAuthor Commented:
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.

zgilboaAuthor Commented:
Thanks for taking the time and looking at this.

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 !!

   x = SourceForm.Recordset.Filter
   SourceForm.Recordset.Filter = ""
   Set RS = SourceForm.Recordset
   RS.Sort = "..."
   Set SourceForm.Recordset = RS
   SourceForm.Recordset.Filter = x
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.
and then i found this recent solution:
works like a charm, major thanks to Willy van Vroenhoven!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.