调整了 .cursertype 的 VBA "rowset does not support fetching backward"错误

Question

使用以下代码打开与 SQL 服务器的连接并将结果直接放入数组中。我的问题是行计数出现错误，我需要重新调整数组。我得到的错误在下面指示的行上并显示

"rowset does not support fetching backward"

我能找到的所有答案都表明光标类型是问题所在，但据我所知，我已经改变了这一点。为长代码道歉，我觉得最好把开头留在里面。

Function ConnectServer() As String()
'Working SQL Server connection

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sConnString As String
Dim strSqlQuery As String
Dim iCols As Long


' Create the connection string.
sConnString = "Provider=SQLOLEDB;Data Source=wait;" & _
              "Initial Catalog=what;" & _
              "User Id=foo;" & _
              "Password=bar;"

' Create the Connection and Recordset objects.
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
conn.CommandTimeout = 50

' Open the connection and execute.
conn.Open sConnString

'    Debug.Print strSqlQuery
Set rs = conn.Execute("SELECT DISTINCT a FROM b")

' Check we have data.
If Not rs.EOF Then
'*****************Problem here********************
    rs.MoveLast
    Debug.Print rs.RecordCount

'Read into array and cleanup...
End If

End Function

我不相信这是这个问题的重复:

Rowset does not support scrolling backward

因为我已经将答案合并到我的代码中并且问题仍然存在。

Answer 1

该问题是由使用 conn.Execute 填充记录集引起的。将记录集的 activeconnection 设置为 ADODB.Connection 并使用记录集的 open 方法将解决此问题。

Function ConnectServer() As String()
   'Working SQL Server connection

    Dim conn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String
    Dim strSqlQuery As String
    Dim iCols As Long

    ' Create the connection string.
    sConnString = "Provider=SQLOLEDB;Data Source=wait;" & _
                  "Initial Catalog=what;" & _
                  "User Id=foo;" & _
                  "Password=bar;"

    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.Connection
    conn.ConnectionString = sConnString
    conn.Open

    Set rs = New ADODB.Recordset

    With rs
        .ActiveConnection = conn
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Source = "SELECT * FROM b"
        .Open
    End With

    ' Check we have data.
    If Not rs.EOF Then

        rs.MoveLast
        Debug.Print rs.RecordCount

        'Read into array and cleanup...
    End If

End Function

您可以使用 Recordset.GetRows() 来填充数组。无需调暗。 GetRows Method (ADO)