By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,811 Members | 785 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,811 IT Pros & Developers. It's quick & easy.

Sorting a recordset by fields in Access (ADO)

P: n/a
Hello All,
I am having problems with sorting a recordset by fields in Access. I
have a table with three columns called ID, SNo and Time and would like
to sort the records by Time. I would like to sort them in the actual
database and not just Order them with SQL. I have included the code I
wrote below. Please bear in mind that I am very new to Access and SQL.
I used the Helpfile of Access for this code but i can't make it work
for me.
Regards,
Alex

////////////////////////////////////////
Sub SortFields()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient
Set cnn = CurrentProject.Connection

rst.Open "SELECT Table1.ID, Table1.SNo, Table1.Time FROM Table1", cnn,
_
adOpenStatic, adLockReadOnly, adCmdText

rst.Sort = "Time DESC"

rst.UpdateBatch
rst.Close
cnn.Close
End Sub
/////////////////////////////////////////
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
You can use Order By to control the order in which the records are returned,
and you can use rst.Sort to sort the records in memory after the records are
returned. In a relational database, by definition, there is no intrinsic
order of records in a table. You are supposed to look at it as a bag of
unsorted records that you can return in any order you like.

On 26 Oct 2004 00:42:10 -0700, pe******@hotmail.com (alex_peri) wrote:
Hello All,
I am having problems with sorting a recordset by fields in Access. I
have a table with three columns called ID, SNo and Time and would like
to sort the records by Time. I would like to sort them in the actual
database and not just Order them with SQL. I have included the code I
wrote below. Please bear in mind that I am very new to Access and SQL.
I used the Helpfile of Access for this code but i can't make it work
for me.
Regards,
Alex

////////////////////////////////////////
Sub SortFields()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient
Set cnn = CurrentProject.Connection

rst.Open "SELECT Table1.ID, Table1.SNo, Table1.Time FROM Table1", cnn,
_
adOpenStatic, adLockReadOnly, adCmdText

rst.Sort = "Time DESC"

rst.UpdateBatch
rst.Close
cnn.Close
End Sub
/////////////////////////////////////////


Nov 13 '05 #2

P: n/a
You must open the proper type of recordset. You have specified a recordset
of type 'readonly', and then tried to update it, which you cannot do.

In the following example, after creating the recordset, I set the Source
property of the recordset first, for clarity. You can also place it in the
recordset's open event, as you have done, but it can get messy if you have a
very long SQL string.
-------------------------------------------------
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Source = "SELECT rev_login from reviewer WHERE rev_login = " &
S2SQL(oldLogin)
rs.Open , cnxn, adOpenKeyset, adLockOptimistic, adCmdText
If Not .EOF Then
rs.Fields(0) = Me.txtLogin
rs.Update
rs.Close
End If
Set rs = Nothing
-------------------------------------------------
The ADO Open event has five necessary parts:

1. The source, which in my example case, I've already supplied separately.
This is usually a SQL string or a table.

2. The active connection, which I called cnxn, and you have as cnn

3. The Cursor Type, one of:
Dynamic, Keyset, Static, or ForwardOnly (all prefixed with 'adOpen').
So far, I have not had any reason to use Dynamic or Static. While a
ForwardOnly cursor is the fastest, it is limited to one trip through the
recordset, 'forward only'. This is just what you need sometimes, but at
other times, use the adOpenKeyset. The performance is the best, and you
have freedom to do anything with the recordset you need.

4. The Lock Type, one of:
BatchOptimistic, Optimistic, Pessimistic, or ReadOnly (all prefixed with
'adLock'). I would recommend that you stick to Optimistic and ReadOnly, and
in fact, usually use Keyset and Optimistic as a pair, and ForwardOnly and
ReadOnly as a pair. If you want to be able to move any direction in a
recordset, and make changes to the data, use adOpenKeyset, with
adLockOptimistic. If you just need to read the data in a recordset as
quickly as possible, for a single or multiple records, use
adOpenForwardOnly, with adLockReadOnly. This will do for 99% of what you
are doing. One problem below is that you have used the line:

rst.UpdateBatch

First, you can't update a ReadOnly recordset. Second, you would only use

rst.Update

Third, as mentioned in Stevespost, you are better off to simply sort the
records in the SQL, as in:

rst.Open "SELECT Table1.ID, Table1.SNo, Table1.Time FROM Table1 ORDER BY
Time DESC"

5. The type of command - ususally adCmdText, for SQL queries, or
adCmdTableDirect, for fast access to a table.

Last, since you are using CurrentProject.Connection, there is no need for
the line:

cnn.Close
Darryl Kerkeslager
"alex_peri" <pe******@hotmail.com> wrote:
I am having problems with sorting a recordset by fields in Access. I
have a table with three columns called ID, SNo and Time and would like
to sort the records by Time. I would like to sort them in the actual
database and not just Order them with SQL. I have included the code I
wrote below. Please bear in mind that I am very new to Access and SQL.
I used the Helpfile of Access for this code but i can't make it work
for me.
Regards,
Alex

'////////////////////////////////////////
Sub SortFields()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset

rst.CursorLocation = adUseClient
Set cnn = CurrentProject.Connection

rst.Open "SELECT Table1.ID, Table1.SNo, Table1.Time FROM Table1", cnn,
_
adOpenStatic, adLockReadOnly, adCmdText

rst.Sort = "Time DESC"

rst.UpdateBatch
rst.Close
cnn.Close
End Sub
'/////////////////////////////////////////

Nov 13 '05 #3

P: n/a
I saw an error and use of an undefined function, and this just cleans up my
example:
Dim cnxn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
rs.Source = "SELECT rev_login from reviewer WHERE rev_login = ' " & oldLogin
& " ' "
rs.Open , cnxn, adOpenKeyset, adLockOptimistic, adCmdText
If Not rs.EOF Then
rs.Fields(0) = Me.txtLogin
rs.Update
rs.Close
End If
Set rs = Nothing
Darryl Kerkeslager
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.