472,127 Members | 1,776 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,127 software developers and data experts.

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


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

Sep 27 '07 #1
0 3015

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

21 posts views Thread by Madingo | last post: by
42 posts views Thread by lauren quantrell | last post: by
52 posts views Thread by Neil | last post: by
reply views Thread by leo001 | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.