Using Access UI Features to Sort a Form Bound to an ADO.Recordset

by Philipp Stiefel, originally published August 23rd 2020, last updated August 24th 2020

Image by congerdesign, used here under Pixabay License

I'm in the middle of a large Microsoft Access ADP to ACCDB migration project. This week I once again encountered a frequent problem in this type of projects. In this article I describe my journey of implementing a workaround. The “solution” is not perfect though, but I hope it is bearable for the users.

The Problematic Scenario

There is one form which is very important to the application. It is displayed as sub form in Datasheet View and uses the SQL Server ROW_NUMBER() function. The input to ROW_NUMBER() is dynamic and determined by VBA variables, which are embedded in the SQL string.

E.g.:

SQL = "SELECT SomeColumns, ROW_NUMBER() OVER (ORDER BY " & vbaVariables & " ) FROM somewhere ...

Based on that SQL an ADO Recordset is created and bound to the form. (I skip the details of this for now. – This text is getting excessively long already.)

Problem: The datasheet should be sortable and filterable by the standard datasheet header functionality. (click on the small downward arrow and use the context-menu-like sort/filter menu). There is also a custom context menu created with macros for sorting and filter by/excluding selection. For best user experience both options should work, but it would be bearable if least one of them would be working.

Out of the box none of this works, because using any sort/filter functionality built into the Access UI causes the error message "Syntax error (missing operator) in query expression ' ROW_NUMBER() OVER (ORDER BY ...'".

This is error message indicates Access, does not understand the SQL that created the recordset. Fair enough, it was written for SQL Server not for Access. The problem is that Access should not evaluate the source SQL of the recordset, instead it should just sort/filter the recordset. The later is possible just using basic ADO functionality independently of the source of the data.

Ruled out Solutions

When working with Microsoft Access ACCDB applications and SQL Server there are two commonly used solutions for encapsulating SQL Server functionality and using the result in data bound Access forms.

Creating a view on the SQL Server and linking the view via ODBC. – This is not practical here, due to the VBA variable input to the function. I would need to create about 8 different views to cover just all current possibilities.

Using a Pass-Through-Query to query the data. – Not practical either, because the data must be updatable but Pass-Through-Queries are read-only by definition.

Another general option would be calculating the row number in an Access query with a sub-select. – But the query would be to complex, and performance would not be acceptable.

Expert Advice

I turned to a group of highly experienced Access experts and asked them for suggestions of alternative solutions. I got some very good advice.

Dale Fye suggested: “populate a temp table with the results of the recordset and use that?”

This would probably be the best solution to such kind of problems. However, I worry about the options for generalization. It would require to also implement a mechanism to update the records in the real underlying tables. This surely can be done, but it requires quite a bit of work to get it right considering concurrency and potential write conflicts.

Crystal Long suggested: “how about adding a field to the table for the temporary order?”

To this very particular problem of the row numbers, this is probably the best solution. However, the row numbers are calculated depending on user input for that user only. So, this approach would require a separate table where there is one record per user and record in the main result set.

I would be tempted to try this. However, the comment reached me only after finishing the implementation described here and after writing the bulk of this text. So, I just add it here for reference.

Ben Clothier suggested: “My suggestion would be to use VBA instead to provide the row numbering, probably the #5 suggestion in this old article: https://accessexperts.com/blog/2015/07/28/ways-to-do-sequential-numbering-in-access/”

In the particular situation the row numbers calculated with ROW_NUMBER() do not necessarily correspond to the position of each record in the result set. So, calculating the row number with VBA would require quite a bit of code to arrive at the intended results. I'm worried that this would not deliver the performance the users expect.

However, the main reason I did not pursue this idea is that it would be only a solution to this very specific problem of row numbers. If I encountered the same core problem with other SQL server functionality, I would need to start again reimplementing existing SQL Server functionality in VBA, which might not be possible for some features.

My Preferred Solution

My preferred solution (in absence of a timely bug fix) would be to somehow catch the user selection in the sort/filter menu, cancel Access' response, and instead just sort/filter the recordset with VBA. - So far, my research did not yield any option to catch that menu's action.

My first idea was to use the Windows API to manipulate the sort/filter menu of the column headers. I discarded that idea because in theory Access itself already supplies the functionality to implement my preferred solution.

Prevent Access from Sorting

The first step is to prevent Access from attempting its ill-behaved sort operation. Before a filter or a sort operation is applied to a form Access raises the OnApplyFilter event of that form. Luckily for me, this event allows to cancel the filtering or sorting. Even more luckily, during the Form_ApplyFilter event procedure the Filter and OrderBy properties are already set to the filter/sort expression that is about to be applied before it is actually processed. This is pure gold for my intentions. This allows me to easily grab the filter or sort order intended by the user. I can then use it for filtering/sorting the recordset with VBA. – More on that later…

First let’s just prevent Access from sorting.

Hmm, that didn’t really help. The syntax error message is shown before this event fires. Thus, it’s not the actual sort operation that is causing the error, but some preparations Access is doing before.

Suppressing the Error Message

Access raises the OnError event of a form before most data related errors are reported as error messages. You can set the Response argument of the Form_Error event procedure to acDataErrContinue to make Access swallow the error message. The syntax error that Access causes on sorting or filtering the data is nothing I would expect to happen during the normal operation of the application. So, I simply instruct Access to always suppress any error with that error number.

The error message is gone. If I disable canceling the filter/sort in Form_ApplyFilter, Access sorts the form correctly. – But only once. After one successful sort operation all further attempts either raise the new error message “Data provider could not be initialized.” or are just completely ignored by Access. – So, the previous step to prevent Access from sorting was not in vain.

That’s more or less what I expected. I’m rather surprised that Access managed to actually sort the form once. So, one more indicator that it’s not Access being unable sort properly, but the problem seems to be that it additionally and unnecessarily messes with the connection of the recordset.

Sorting and Filtering the Form Recordset – First Try

Now that I’ve suppressed the error and prevent Access from mis-sorting the form, I can focus on the actual work to sort and filter the data. This should be easy now:

Nope! – This would have been too easy. Sorting or filtering the form recordset while Access is about to do the same (= in the Form_ApplyFilter event procedure) crashes Access instantly.

I fully understand that Access does not like me throwing a spanner in its works, but a simple error message would have been a more appropriate response than a full crash. – Never mind!

So, we need to run code that is triggered by the OnApplyFilter event but it must not be called by the Form_ApplyFilter event procedure. – I don’t have the perfect solution for this, but I know a feasible workaround.

Using OnTimer Event for Decoupling

It’s certainly not elegant, but it’s fairly easy to implement. If you want to run code but it must not be called directly from the event procedure that invoke the currently running code, you can leverage the OnTimer event of a form for that.

The implementation has some minor inconveniences but is simple. You put the code you want to run into the timer event of a form. That might even be a hidden form. The TimerInterval of the form is set to 0 in its design. This means the OnTimer event will never fire. If you want to run that code, you set the TimerInterval to a value slightly higher than 0; TimerInterval is measured in milliseconds. The event fires after the time elapsed. The first thing you do in the Form_Timer event procedure is setting TimerInterval back to 0 to prevent the timer event from firing again. Then you run the code doing actual work.

One inconvenience, beyond that fact that this code structure is not intuitively understandable, is that we cannot pass any arguments directly to the Form_Timer event procedure. We need to use class module level variables in the form to pass data to this code. In this case the data is the sort and filter expression (m_proposedSort and m_proposedFilter).

We will look at the SortForm method in a minute.

Revisiting Form_ApplyFilter

The last time I looked at Form_ApplyFilter, it crashed Access. Meanwhile I implemented the code to prevent this. Now we need to call that code and pass it the proposed new sort and filter expressions.

However, there is another small problem that needs to be addressed. If the ApplyFilter event is canceled while a filter was about to be applied (ApplyType = acApplyFilter), Access will invoke the ApplyFilter event again immediately afterwards with ApplyType = acShowAllRecords, to indicate that the filter is removed again. If I would not distinguish these two different situations, I would filter the recordset and then remove the filter immediately afterwards. – Pointless.

Here is the code, that only triggers filtering the recordset upon user action but not on Access indicating the removed filter. If the user removes the filter and the event is canceled it is not invoked again.

Finally: Sorting and Filtering the Form Recordset

I spent a lot of time just dealing with obstacles that kept me from sorting the form. Now is the time to write the actual code for sorting and filtering the recordset.

The code is mostly straight forward if you are aware of a couple of differences between Access filtering and ADO.Recordset filtering.

• Access prefix column references with the form name reference ([FormName].[ColumnName]). ADO knows nothing about forms, so I remove the form name.
• Access uses the ALIKE operator in filter expressions (at least in SQL92 compatibility mode), which must be changed to a simple LIKE for ADO.
• Double quote delimiters need to be replaced with single quotes and unnecessary parenthesis around expressions need to be removed. – My code for that is very simplistic and “brute force”. It will lead to incorrect results when it replaces/removes characters inside string literals. I will revisit and improve this code, but for now it is “good enough”.
• ADO has the very weird implementation of checking for NULL with explicit equal or unequal operators. We need to replace IS (NOT) NULL accordingly. – Keep in mind that this is a huge exception to the rule that you cannot compare NULL for (un)equality with anything.
• Sometimes an ADO Recordset’s Filter property is set to "0" when it has no filter. – I’ve got no idea what this is about, I just ignore/remove the "0".

With all this in mind, here is the code:

Please be aware that I deliberately changed the sort precedence in my code. The column first selected for sorting has the highest precedence and additional columns selected added after that are added with decreasing precedence. I prefer it this way, but the default behavior of Access is the other way round.

If you wonder about BaseRecordset in the above code, that is a custom property I created to store a clone of the form’s recordset. I use that instead of the form’s actual recordset to start of with a fresh untouched clone of the original recordset, just in case the form recordset gets put in a messed up state by any of the operations.

Flaws and Limitations

The code above is what will be released to users for acceptance testing. However, there still are a few flaws and limitations. I am well aware of these but fixing them will be extremely difficult if not impossible. I hope users will be able to bear with them.

The small visual icon (arrow or funnel) for sorted or filtered columns in the column header are missing. Access is not aware of the applied filter or sort order after all. For the same reason, the “Clear filter” menu item is disabled in the column header filter menu. (Users can remove the filter using the context menu.)

ADODB.Recordsets do not support an “Ends with …” filter. Using this type of filter will raise an error.

The most problematic flaw is that when filters (not sorting!) in the column header menu are used, the filtering works, but a message “Enter a valid value” is displayed followed by the input box to reenter the filter criteria. – I guess, this is because Access thinks ApplyFilter was canceled due to invalid user input. I’m still thinking about a solution to this problem.

Of course, this is a serious flaw. I think, it is acceptable in my scenario because I assume the users will more likely use the context menu filter, which works flawlessly now, instead of the column header filter.

Should you only be interested in sorting your form, but not filtering it, you can just set the AllowFilters property of the form to false and the problematic functionality is completely disabled.

Conclusion

This is a ton of non-intuitive code to re-implement something that is normally built into Microsoft Access and working fine. Is it really worth binding a Form to an ADODB.Recordset, when this added complexity and the remaining flaws are involved? This is a difficult question! In some scenarios I think, it is worth it. If you need a writeable form bound to SQL Server data that requires SQL Server functionality in it’s SQL there are few other options. Storing the data in a temporary table and binding to that instead, as suggested by Dale Fye, is the only one I can think of. But with that approach writing record changes back to the database also means reimplementing functionality that you would normally get out-of-the-box.

I hope you enjoyed reading this and find some interesting Access and VBA coding ideas while following my strategy of working on this problem.

Share this article: