If I do Dim rsDoc as NEW ADODB.R

[Guest_imported]

If I do

Dim rsDoc as NEW ADODB.Recordset

str = "SELECT * FROM Mytable WHERE ID = '" & varID & "' ORDER BY ID"

With rsDoc
.Open str, cn, adOpenStatic, adLockReadOnly, adCmdText
msgbox(.RecordCount)
End With

I get a recordcount=1 when there are NO RECORDS on the record!!! Why does this happen? I should get a zero record count!

 

[woyler]

Try
.Open str, cn, adOpenKeyset, adLockReadOnly, adCmdText

 

[Niv3k]

or try

If not (.EOF and .BOF) then
.movelast
msgbox .recordcount & " records!"
Else
msgbox "0 records!"
End If

I believe ADO has trouble determining the number of records until it's moved through the entire recordset if you use a static recordset.

Kevin

 

[Guest_imported]

No. The problem is on the Distinct clause.
Sorry I posted wrong, the error happens when I do:

str = "SELECT DISTINCT * FROM Mytable WHERE ID = '" & varID & "' ORDER BY ID"

or

str = "SELECT DISTINCT FIELD2 FROM Mytable WHERE ID = '" & varID & "' ORDER BY ID"

or anything with the DISTINCT clause

Because if I do only

str = "SELECT * FROM Mytable WHERE ID = '" & varID & "' ORDER BY ID"

This will work!
But if I use DISTINCT if Mytable is empty the .RecordCOunt will return 1 instead of 0 and of course .BOF will be false!!!
This is one of those cases when I start thinking what the heck is going on here?
Because I'm all out of ideas here.

Please help me out if you can.