Recordset problem in Access database

  • Thread starter Thread starter George
  • Start date Start date
G

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
 
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
Click to expand...

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
 
Back
Top