Rs.Recordcount returns a value of -1

[dhaveedh]

Hi,

I have an ADODB connection to an Oracle table from VBA code and I want to retrive matching recordsets from the tables into another.


Here is my code;

Set db = CurrentDb
Set rs1 = db.OpenRecordset("ImageA")


Set con = New ADODB.Connection

Set rs = New ADODB.Recordset

sConnection = "Provider=MSDAORA;Data Source=Polite;User ID=test;Password=testMe;"

con.Open sConnection

Do Until rs1.EOF

sSQL = SELECT a.CG_DATE as TxnDate, a.AMT as TxnAmt,a.REF
FROM CASH_PAY as a, CM_DATA as b, CG_ACCOUNT as c
WHERE a._REF = b._REF AND c.REF = b._REF
AND c.B_SENT_IND = 0
AND c.LIVE = 1
AND a.CG_DATE >= Date - 70 AND a.CG_DATE <= Date

rs.Open sSQL, con

If rs.RecordCount <> 0 Then
rs1.Edit
rs1.Fields!Txn = rs.Fields("TxnAmt")
rs1.Fields!TDate= rs.Fields("TxnDate")
rs1.Update

Do Until rs.EOF
UpdatePayment rs1.Fields("Ref"), rs.Fields("TxnDate"), rs.Fields("TxnAmt")
rs.MoveNext
Loop

End If
rs.Close
rs1.MoveNext
Loop

Can anyone help me with why I get my recordcount to be 0. The error message says EOF OR BOF may have been reached. I have tried with rs.EOF and it does not get into the rs.Edit section.

Thanks



KISS - Keep It Simple Sugar!

 

[Golom]

Check out ADO HELP. The Recordcount property will return -1 for certain types of recordsets and cursors. You usually need to use a server-side cursor and a KeySet or ForwardOnly type recordset to get a count for Recordcount.

In your code just test

[blue][tt]
If NOT rs.EOF Then
[/tt][/blue]
since you don't need the recordcount. You're just testing if there are any records in the recordset and EOF will take care of that.