ADODB Recordset.RecordCount给出错误的答案

I have an issue with my Recordset returning -1 as the RecordCount when in fact there are 1164 records.

我有一个问题,我的Recordset返回-1作为RecordCount,实际上有1164条记录。

I have checked my query which is fine. Even though it tells me there are -1 records the CopyFromRecordset method still works and pastes the correct results. Never had an issue before using the rs.RecordCount?

我检查了我的查询,这很好。即使它告诉我有-1条记录,CopyFromRecordset方法仍然有效并粘贴正确的结果。在使用rs.RecordCount之前从未遇到过问题?

Dim strSQL As String
Dim rs As New ADODB.Recordset

If cust = "JPM" Then
    port = "RP L99"
Else
    port = "RP V10"
End If

strSQL = "my select query"

rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic
rs.MoveFirst
GetCompanies = rs.RecordCount
If GetCompanies > 0 Then
   wsLive.Range("A" & ROWCOMPANYSTART).CopyFromRecordset rs
   wsLive.Range("C" & ROWCOMPANYSTART & ":C" & GetCompanies + ROWCOMPANYSTART).NumberFormat = "0.00%"
End If
CloseRecordset rs

End Function

1 个解决方案

#1

To get the actual count, you need to first move the cursor to the last record then move back to the beginning. However, remember for a large dataset, the moving back and forth will be an inefficient task. So, I would suggest the following with caution.

要获得实际计数,您需要先将光标移动到最后一条记录,然后再移回到开头。但是,请记住,对于大型数据集,来回移动将是一项低效的任务。所以,我会谨慎地提出以下建议。

    Dim strSQL As String
    Dim rs As New ADODB.Recordset

    If cust = "JPM" Then
        port = "RP L99"
    Else
        port = "RP V10"
    End If

    strSQL = "my select query"

    rs.CursorType = adOpenDynamic

    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

    If Not (rs.BOF And rs.EOF) Then
        rs.MoveLast
        rs.MoveFirst
        GetCompanies = rs.RecordCount
        wsLive.Range("A" & ROWCOMPANYSTART).CopyFromRecordset rs
        wsLive.Range("C" & ROWCOMPANYSTART & ":C" & GetCompanies + ROWCOMPANYSTART).NumberFormat = "0.00%"      
    'Else
        'MsgBox "No Records !!"
    End If

    CloseRecordset rs
End Function