ADO recordset problem

[paulquinlan100]

Hi

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

Dim con As New ADODB.Connectio n

con.Open _
"Provider=Micro soft.jet.oledb. 4.0; Data Source=\
\feltfps0002\ge ngrpshare0009\S MP\CONFIDENTIAL \Programme office\PIP
\PIP.mdb"

sql07 = "select * from qryBBLatestDate s Where LatestViewDate
between #04/01/2007# and #03/31/2008# and Type_of_site = " &
"""Extensio n""" & ""

rstYear07.Open sql07, con, adOpenForwardOn ly, adLockReadOnly

If rstYear07.Recor dCount 0 Then
rstYear07.MoveF irst
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

[lyle fairfield]

"pa************ @hotmail.com" <pa************ @hotmail.comwro te in
news:11******** **************@ k79g2000hse.goo glegroups.com:

If rstYear07.Recor dCount 0 Then
rstYear07.MoveF irst

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 adOpenForwardOn ly.

You could change the cursor type to
adOpenKeyset
adOpenDynamic
or
adOpenStatic
with rstYear07.Open sql07, con, adOpenSomething Else, 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.Recor dCount 0 Then
rstYear07.MoveF irst

(and the corresponding EndIf)

They are are redundant.

--
lyle fairfield

[paulquinlan100]

Thanks Lyle, problem solved!
On 7 Nov, 11:14, lyle fairfield <lylef...@yahoo .cawrote:

"paulquinlan... @hotmail.com" <paulquinlan... @hotmail.comwro te innews:11****** *************** *@k79g2000hse.g ooglegroups.com :

If rstYear07.Recor dCount 0 Then
rstYear07.MoveF irst

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 adOpenForwardOn ly.

You could change the cursor type to
adOpenKeyset
adOpenDynamic
or
adOpenStatic
with rstYear07.Open sql07, con, adOpenSomething Else, 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.Recor dCount 0 Then
rstYear07.MoveF irst

(and the corresponding EndIf)

They are are redundant.

--
lyle fairfield