ADODB连接Oracle时跨机器出现Recordset仅检索首行的问题排查

• 开发VBA功能：从Oracle SQL数据库检索记录并写入Dictionary字典
• 核心异常：同一段代码本机运行完全符合预期，同事电脑运行异常：哪怕Recordset对象的RecordCount属性值大于1，也仅能检索到1条记录；同一个SQL查询在本机可正常返回多条记录
• 已尝试的排查动作：最初怀疑是MoveNext方法导致的问题，将遍历逻辑从MoveNext切换为GetRows方法批量取数，问题依然存在。因代码在本机运行正常，确认代码逻辑本身无错误

WriteQueryToDict函数 #

Public Function WriteQueryToDict(SQL As String) As Dictionary
    'First column of the query must contain key that will be used to locate records in the dictionary
    'Output is a dictionary with first column as key, and the rest in an array that is indexed from 0 
    Dim Results As Recordset
    Dim ResultsArray() As Variant
    Dim cursorField As Integer
    Dim cursorRow As Integer
    Dim Output As Dictionary
    Dim key As Variant
    Dim record() As Variant
    
    Set Results = RunQuery(SQL)
    
    Set Output = New Dictionary
    If Not (Results.BOF And Results.EOF) Then
        ' New loop
        ResultsArray = Results.GetRows()
        ReDim record(0 To (UBound(ResultsArray, 1) - 1))
        
        For cursorRow = 0 To UBound(ResultsArray, 2)
            key = ResultsArray(0, cursorRow)
            For cursorField = 1 To UBound(ResultsArray, 1)
                record(cursorField - 1) = ResultsArray(cursorField, cursorRow)
            Next
            Output.Add key, record
        Next
        ' Original loop
        'Results.MoveFirst
        'Do
        '    For cursorField = 1 To Results.fields.Count - 1
        '            record(cursorField) = Results.fields(cursorField).Value
        '    Next
        '
        '    key = Results.fields(0)
        '    Output.Add key, record
        '    Results.MoveNext
        'Loop Until Results.EOF
    End If
    Set WriteQueryToDict = Output
End Function

RunQuery函数（执行数据库连接与查询） #

Private Function RunQuery(SQL As String) As Recordset
    Dim Username As String
    Dim Password As String
    Dim DBConn As ADODB.Connection
    
    Username = ThisWorkbook.Sheets("Configuration").Range("C3").Value
    Password = ThisWorkbook.Sheets("Configuration").Range("C4").Value
    Set DBConn = New ADODB.Connection
    
    With DBConn
        .Provider = "OraOLEDB.Oracle.1;user id = " & Username & "; password = " & Password
        .CONNECTIONSTRING = "Data Source=" & DB
        .Open
    End With
    
    Set RunQuery = DBConn.Execute(SQL)
End Function

• 初步推断问题与代码外的环境配置有关，但同事不具备自主配置Oracle驱动的能力，此前未遇到过同一套代码在不同机器上出现如此大的行为差异，需要明确问题排查的关键方向。

• 在WriteQueryToDict函数的循环启动位置设置断点调试，可直接操作Recordset对象：
  • 同事电脑运行时，曾确认RecordCount属性值约为60
  • 调用MoveFirst后，预期可连续执行60次MoveNext直到EOF变为真，实际仅执行1次MoveNext后EOF就变为真
  • 调试过程中曾尝试将cursorLocation设置为Client，问题仍未解决

内容的提问来源于stack exchange，提问作者sgp667