ADO recordset problem

Hi

I'm using the following code to retrieve some data from a seperate DB:

Dim con As New ADODB.Connection

con.Open _
"Provider=Microsoft.jet.oledb.4.0; Data Source=\
\feltfps0002\gengrpshare0009\SMP\CONFIDENTIAL\Programme office\PIP
\PIP.mdb"

sql07 = "select * from qryBBLatestDates Where LatestViewDate
between #04/01/2007# and #03/31/2008# and Type_of_site = " &
"""Extension""" & ""

rstYear07.Open sql07, con, adOpenForwardOnly, adLockReadOnly

If rstYear07.RecordCount > 0 Then
rstYear07.MoveFirst
Do While Not rstYear07.EOF

..........


However, it wont seem to retrieve the data, as the recordcount is
always -1. Ive pasted the code into a query and it does pull out of a
number of records so i cant figure out where im going wrong. Any
ideas?

Thanks for any suggestions

Paul

Re: ADO recordset problem

"paulquinlan100@hotmail.com" wrote in
news:1194433337.121159.279070@k79g2000hse.googlegroups.com:

> If rstYear07.RecordCount > 0 Then
> rstYear07.MoveFirst

From ADO help:

"The cursor type of the Recordset object affects whether the number of
records can be determined. The RecordCount property will return -1 for a
forward-only cursor; the actual count for a static or keyset cursor; and
either -1 or the actual count for a dynamic cursor, depending on the data
source."

The default cursor type is adOpenForwardOnly.

You could change the cursor type to
adOpenKeyset
adOpenDynamic
or
adOpenStatic
with rstYear07.Open sql07, con, adOpenSomethingElse, adLockReadOnly

and you may need to for other reasons, but if you just want to move through
the recordset you could just remove the two lines

> If rstYear07.RecordCount > 0 Then
> rstYear07.MoveFirst
(and the corresponding EndIf)

They are are redundant.

--
lyle fairfield

Re: ADO recordset problem

Thanks Lyle, problem solved!


On 7 Nov, 11:14, lyle fairfield wrote:
> "paulquinlan...@hotmail.com" wrote innews:1194433337.121159.279070@k79g2000hse.googlegroups.com :
>
> > If rstYear07.RecordCount > 0 Then
> > rstYear07.MoveFirst
>
> From ADO help:
>
> "The cursor type of the Recordset object affects whether the number of
> records can be determined. The RecordCount property will return -1 for a
> forward-only cursor; the actual count for a static or keyset cursor; and
> either -1 or the actual count for a dynamic cursor, depending on the data
> source."
>
> The default cursor type is adOpenForwardOnly.
>
> You could change the cursor type to
> adOpenKeyset
> adOpenDynamic
> or
> adOpenStatic
> with rstYear07.Open sql07, con, adOpenSomethingElse, adLockReadOnly
>
> and you may need to for other reasons, but if you just want to move through
> the recordset you could just remove the two lines
>
> > If rstYear07.RecordCount > 0 Then
> > rstYear07.MoveFirst
>
> (and the corresponding EndIf)
>
> They are are redundant.
>
> --
> lyle fairfield