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
'/////////////////////////////////////////
