ADO recordset problem

[paulquinlan100]

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\Prog ramme 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

[lyle fairfield]

"pa************@hotmail.com" <pa************@hotmail.comwrote in
news:11**********************@k79g2000hse.googlegr oups.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

[paulquinlan100]

Thanks Lyle, problem solved!
On 7 Nov, 11:14, lyle fairfield <[e-mail]:

"[e-mail]" <[e-mail] innews:11**********************@k79g2000hse.google groups.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