Recordset problem in Access database

[George]

Hi,
I have following code which runs fine:
------------------------------------
Dim cnn As Connection
Dim rsStudent As ADODB.Recordset
Dim strStudentSQL As String

Set cnn = CurrentProject.Connection
Set rsStudent = New ADODB.Recordset
strStudentSQL = "Select * from students"
rsStudent.ActiveConnection = cnn
rsStudent.Open strStudentSQL
MsgBox rsStudent.Fields("StdID")
------------------------------------
But it seems some properties of the recordset such as "moveLast",
"recordcount" and etc are not available. The recordcount always return -1,
and when excute "MoveLast" or "MovePrevious" , I got error
message:"Operation is not allowed in this context".
I think this is because of the connection properties, but it is not allowed
to change the connectionstring when the connection is open.
How to fix this problem?
Thanks!
George

 

[Tim Ferguson]

rsStudent.Open strStudentSQL ....

But it seems some properties of the recordset such as "moveLast",
"recordcount" and etc are not available. The recordcount always return
-1, and when excute "MoveLast" or "MovePrevious" , I got error

I am very far from being an expert in ADO, but I think there are more
arguments to the adodb.recordset.open method than one. Have you made sure
that it expects a record-returning command (ReturnsRecords:=True) and that
you have set a text sql command (Type:=adCmdText) and so on?

Failing that, rst.MoveLast and MovePrevious are meaningless in an unordered
recordset:- if you want to count the students you need something like

strSQL = "SELECT COUNT(*) AS Expr001 FROM Students"

Hope that helps


Tim F