ODBC recordcount/move last prob

[suchin]

private sub main
dim s as string
s="ODBC;DATABASE=;UID=;PWD=;DSN=prog;"
set ws =workspaces(0)
set db=ws.OpenDatabase("",dbDriverComplete,False,s)
set rs=db.OpenRecordSet("Select * from Loc",dbOpenDynaset,dbRunasync,dbOptimistic)
rs.MoveLast
Msgbox rs.RecordCount
end sub
ws,rs,db are defined public

The Problemmo:
well the recordcount shows -1 despite moving last.No other combo of type,options, in "set rs " works with odbc with access as the back end oh btw this also has to be compatible with SQL 7.0
I have also noticed that this is true for only the tables with more than a 100 records is there some option that has to be changed
Solutions required very urgently

[Clunietp]

are you stuck using DAO?

DAO isn't optimized for client/server databases, you are much better off using ADO -- the provider apparently doesn't support the recordcount property, as dbOpenDynaset is equivalent to a Keyset cursor, which would normally be able to give you a recordcount.....

Can you convert to ADO at this point?

If not, you might have to do a loop to find out the recordcount
ex:
do until rs.eof
counter = counter + 1
rs.movenext
loop

good luck!

[RogerH]

Besides of using ADO which is much better then DAO: I found it usefull just to use a

SELECT COUNT(*) FROM TABLE

even if I need the recordset later. It's much faster than lopping through the records or using MoveLast (if it works anyway).

Roger