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

Posted on 2008-09-30
Last Modified: 2013-12-05

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

Question by:zgilboa
LVL 84
ID: 22612754
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 ...

Author Comment

ID: 22616754
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,
LVL 84
ID: 22617036
Wondering if the Continuous form aspect has anything to do with this ... can you temporarily change to Normal view and see what happens.
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)


Author Comment

ID: 22620495
tried that too... wish it were that easy:)
LVL 84
ID: 22622447
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.

Author Comment

ID: 22625376
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?
LVL 84
ID: 22626882
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

Author Comment

ID: 22627833
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...
LVL 84
ID: 22628045
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.

Author Comment

ID: 22628687
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.

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 22628941
<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.

Author Comment

ID: 22629616
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.


Expert Comment

ID: 23293144
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

Expert Comment

ID: 23679738
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!

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Trying to get public variables with login info accessible across entire project. 2 18
Top 1 of each supplier 55 61
Dlookup MSACCESS 5 25
SQL multicriteria from ONE textbox 32 44
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question