Access 2003 form bound to a fabricated ADO recordset: problem with sorting and filtering

[Yarik]

Hello,

Here is a sample (and very simple) code that binds an Access 2003 form
to a fabricated ADO recordset:

' Create recordset...
Dim rs As ADODB.Recordset: Set rs = New ADODB.Recordset

' Append one or more fields...
Call rs.Fields.Append("Number", adInteger)

' Open recordset and add a few records...
Call rs.Open
Dim num as Integer
For num = 1 to 10
Call rs.AddNew
rs("Number") = num
Call rs.Update
Next
Call rs.UpdateBatch

' Bind the form to the recordset...
Set Me.Recordset = rs

This seemed to make everything work just fine until I've discovered
that form-level sorting and filtering with such recordset does not
work. Worse yet, it makes Access 2003 fail miserably (like a GPF in
old-good days).

Accidentally, our team discovered that the abovementioned problem goes
away entirely if you do the following trick: after appending the
fields to the recordset and before opening it, assign some non-blank
string to the recordset's Source property. Interestingly enough, the
contents of this string almost does not matter. For example, if you
add the following line just before a call to rs.Open:

rs.Source = "Microsoft Access Sucks"

Tada! Now the form can be sorted and filtered without a hitch. Single
character string also works fine. (Based on some experiments, it looks
like any string would do so long as it looks like a valid SQL
statement after a very superficial syntactic check by Access.)

The abovementioned hack (or cheat) is easy and may be funny, but... it
is still looks, smells, and tastes as a hack. Although it does help
with sorting and filtering, it may leave other issues (yet unknown to
us) unresolved or maybe even induce some new issues. So I am wondering
if anyone experienced the same problem and knows a solution better
than the trick described above?

Thnak you,
Yarik.