Row Return Failure

[Deltaflyer]

Dim db As Database
Dim rs As Recordset

Set db = Workspaces(0).OpenDatabase(Locale + "\vbft.mdb&quot
sSQL = "Select * from details"
Set rs = db.OpenRecordset(sSQL, 2)

With this coding i am trying to return 400+ rows, there are 400+ rows in the table, but whenever i execute this i get returned only 1 row. i.e. rs.recordset=1

Can anybody help me out here? DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[sunaj]

Hi,

It looks like you use DAO and I don't think that recordsets in dao have a .recordset property, do you mean .recordcount?

You open your recordset as dynaset. I don't know if this type of recordset supports .recordcount. Try with a snapshot recordset. Have you tried moving through the recordset to seem how many rows it actually contains?

i=0
rs.movefirst
while not rs.eof
rs.movenext
i=i+1
wend
msgbox(i)


Sunaj

 

[Deltaflyer]

Sorry, my mistake i meant rs.recordcount.

Here is the rough idea of the next few lines.

MsgBox (rs.RecordCount) 'to show the recordsreturned
For i = 1 To rs.RecordCount
Field1.Caption = rs!ID
Field2.Caption = rs!wkstkarea
Field3.Caption = rs!Description
rs.MoveNext
Next

However this is only displaying 1 record. The first one in the dbtable.

Is there another way i can access the db other than DAO, i have heard about rdo but cannot figure out how to use it. Would this resolve my problem? If so could you point me in the direction of an example if you know of one.

Thanks for your help, DeltaFlyer
The Only Programmer To Crash With Style. LOL

 

[sunaj]

Hi,

Yes you can use ADO which is replacing DAO. If you're just starting your project I would recommend to swich to ADO (ActiveX Data Ojects).

Anyway the problem is the same, you must have a provider that supports .recordcount and you must use a cursortype that allows returning a .recordcount value (in ADO you can use 'static').

Unless you really need the recordcount, try something ike this instead:

----------------------------------------------------
if not( rs.eof and rs.bof) then
rs.movefirst
while not rs.eof
'do your stuff here
rs.movenext
wend
else
msgbox("empry recordset&quot
end if
-----------------------------------------------------

In that way you don't need the recordcount at all (and its faster).
By the way: what the point in looping through the recordset and setting some captions? The captions will end up with the values of the last row in the recordset, in whixh case you might as well just use .movelast.

Sunaj

 

[Deltaflyer]

thanks, static worked. I am using field arrays to display a load of data. i.e. field1(1) = rs!record1, field1(2) = rs!record2. They will not be overwritten. Looks like my problem is fixed.

Thanks for all your help sunaj, DeltaFlyer
The Only Programmer To Crash With Style. LOL