Failure to sort recordset using VBA

[Richard S.]

When trying to sort an Access database with the following extracts of code, I
get this message: "Current provider does not support the necessary
interfaces for sorting or filtering."

What must I do to sort the records using VBA? I will appreciate any help to
be able to accomplish the sort and to understand why this does not work.

Dim rst As New ADODB.Recordset

Dim strSort As String ' String for Sort parameters
strSort = "MbrID ASC, ChgDate ASC, ActionType ASC"

rst.Open "UpdatesIn", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic

rst.Sort = strSort

[Stephen Howe]

Post by Richard S.
What must I do to sort the records using VBA? I will appreciate any help to
be able to accomplish the sort and to understand why this does not work.

Sort only works when the recordset is client-sided.
You have opened a server-sided recordset.
See
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdprosortpropertyado.asp

The recordset properties

Sort
Filter
Find

work best for client-sided Recordset (for some properties,they _ONLY_ work
for client-sided Recordsets).

Note, these properties are very limited, Find does not work on multiple
fields.
So if they do what you want - great!!!
But if not, you are better off selecting your records using SQL and using
the power of WHERE and ORDER BY clauses.

All the different ways of opening a Recordset : CursorLocation, CursorType,
LockType have an impact on what Recordset properties are supported and the
speed of the Recordset. They all have different strengths and weaknesses.
Generally don't open a recordset with more functionality than you need as it
costs more.
e.g. Not planning on updating? - then use adLockReadOnly

Programmers here make the big mistake of defaulting on the arguments to
Recordset.Open(), not realising that it is _THE_ single most important
method of Recordset, as it directly affects subsequent properties and
functionality it has.
For instance, RecordCount can return -1 which entirely depends on how you
open the Recordset. All documented.
The defaults that Microsoft have provided are fine, it is just that they are
not suitable for _ALL_ situations.

Cheers

Stephen Howe

[Bob Barrows [MVP]]

Post by Richard S.
When trying to sort an Access database with the following extracts of
code, I get this message: "Current provider does not support the
necessary interfaces for sorting or filtering."
What must I do to sort the records using VBA? I will appreciate any
help to be able to accomplish the sort and to understand why this
does not work.
Dim rst As New ADODB.Recordset
Dim strSort As String ' String for Sort parameters
strSort = "MbrID ASC, ChgDate ASC, ActionType ASC"
rst.Open "UpdatesIn", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Sort = strSort

Why sort the recordset _after_ retrieving the records? Why not supply an
ORDER BY clause for the query used to open the recordset?

If you are getting input from a user to determine the sort order, then you
will need to validate the input to prevent sql injection, but this should
work with your code as written:

Dim sql as string
sql = Select * from UpdatesIn order by " & strSort
rst.Open sql, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

[Richard S.]

Bob, your response resolved my problem. Thank you very much for taking the
time to answer my question.

Post by Bob Barrows [MVP]

Post by Richard S.
When trying to sort an Access database with the following extracts of
code, I get this message: "Current provider does not support the
necessary interfaces for sorting or filtering."
What must I do to sort the records using VBA? I will appreciate any
help to be able to accomplish the sort and to understand why this
does not work.
Dim rst As New ADODB.Recordset
Dim strSort As String ' String for Sort parameters
strSort = "MbrID ASC, ChgDate ASC, ActionType ASC"
rst.Open "UpdatesIn", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
rst.Sort = strSort

Why sort the recordset _after_ retrieving the records? Why not supply an
ORDER BY clause for the query used to open the recordset?
If you are getting input from a user to determine the sort order, then you
will need to validate the input to prevent sql injection, but this should
Dim sql as string
sql = Select * from UpdatesIn order by " & strSort
rst.Open sql, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"