ADO reference returns -1 recordcount

[sultan]

Hi
The following code displays the results howver the recordcount property of the ado recs recordset returns -1. There are over 100 records in the resultset!

Set Cons = New Connection
Set Recs = New Recordset

Cons.Open "provider=MSDASQL;dsn=accessextreme", "admin", ""
Recs.Open "select * from customer", Cons, adOpenDynamic, adLockOptimistic

Text1.DataField = "city"
Set Text1.DataSource = Recs



Can anyone please give help in this?

[JHausmann]

You need to loop through the data.

add the following:

If Recs.EOF = False Then 'we got a count, this will tell us
Do While Not Recs.EOF
'do something with the data retrieved
Recs.MoveNext
Loop
End If

[sultan]

Hi JH

Well I did try : recs.MoveLast and recs.MoveFirst

but even after this the recordcount value is the same = -1!

Latest: Also just now tried what you have suggested above no luck I am affraid.

[Edited by sultan on 07-27-2000 at 12:03 PM]

[JHausmann]

Heh, OOps, I missed that one.

To get the record count, you could always issue

"select count(*) from customer"

[sultan]

It didn't work, any more ideas?



This is really bewildering!

[JHausmann]

It's not going to set "recordcount" but return a value that will represent what recordcount should have.

[sultan]

Sorry What I meant was that I could not reset the recordset it complains that the recordset is already open. I know this is a silly question but, how do you feed a new select query to the already open recordset?

many thanks.

[JHausmann]

I just open/close recordsets as many times as I need them.

[ofert]

Here are 2 options out of some:
1) rs.open "SELECT COUNT(*) FROM mytable"
msgbox rs.fields(0) 'Your recordcount
2) On your original rs, using adOpenStatic Cursor will do the change.
Good Luke !

[Clunietp]

Open the recordset using a client side static cursor. If this does not work, it is a deficiency in your ODBC driver.