ADODB RecordSet.RecordCount属性始终返回-1
问题描述:
下面的代码正确导出数据,但是我的debug.print
行总是给出-1。我想检索debug.print
声明中的行数。请提出什么是错的。ADODB RecordSet.RecordCount属性始终返回-1
Sub TEST()
Dim rs As Object
Dim iCols As Integer
Set rs = CreateObject("ADODB.Recordset")
On Error GoTo ERR
Dim SQLSTR As String, MYVAL As String
MYVAL = InputBox("Enter Query")
SQLSTR = " " & MYVAL & ""
CONNECT_TO_DWHS
rs.Open SQLSTR, PERSONALDBCONT
For iCols = 0 To rs.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Debug.Print rs.RecordCount
ActiveSheet.Cells(2, 1).CopyFromRecordset rs
ActiveSheet.Cells(1, 1).Select
CLOSE_CONNECTION_TO_SQL
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
.FreezePanes = True
End With
Exit Sub
ERR:
CLOSE_CONNECTION_TO_SQL
MsgBox "There was an error"
End Sub
答
尝试指定光标位置。在打开连接之前指定光标位置。
rs.CursorLocation = adUseClient
rs.Open SQLSTR, PERSONALDBCONT
For iCols = 0 To rs.Fields.Count - 1
ActiveSheet.Cells(1, iCols + 1).Value = rs.Fields(iCols).Name
Next
Debug.Print rs.RecordCount
请阅读这篇文章:http://www.geeksengine.com/article/recordcount-ado-recordset-vba.html
我认为这是因为在FOR操作中发生的递减。为了使循环停止,最后执行的操作是减少1个'rs.Fields.Count'值,为了循环结束,该值为'-1'。因此,*我认为*'print'会一直显示-1。 (*但这只是一个想法,它完全不是程序员的回应类型,应该是精确*)。为了检查这一点,我建议你在循环内移动'print',或者甚至可以在循环之前打印记录数。 –