SQL查询转换为VBA数组

Question

正如标题所示，我正在研究将查询内容存储到数组中的不同方法。我已经尝试了不同的方法，但似乎大多数方法的输出都是不正确的。这当然是由于我缺乏适当理解所致，因此在尝试了一段时间后，我决定询问什么是最好的方法？我将与您分享我迄今为止的一些方法，您可以看到我的调查结果。

Dim MyArray() As Variant
MyArray = rst.GetRows(rst.RecordCount)

这个方案还可以，但是它将所有信息垂直存储而非水平。有没有办法翻转它? 是通过使用ReDim实现的吗? 还是因为行被存储在数组维度中，所以自然是垂直的?

        Index = 0
        Do While Not rst.EOF
                ReDim Preserve MyArray(1, Index)
                MyArray(0, Index) = CStr(rst.Fields(0).Value)

                'Safety check to make sure the value isn't null (was having problems before)
                If rst.Fields(1).Value <> vbNullString Then
                    MyArray(1, Index) = CStr(rst.Fields(1).Value)
                End If
            Index = Index + 1
            rst.MoveNext
        Loop

        sheet.Range("a1:ba10000").Value = MyArray

这个问题是垂直存储数据，但无法正确输出记录，每个记录只输出了前两列信息，其余的被输出为 #N/A#。我想我的原始方法更接近正确答案，但我决定尝试一些实验来找到解决方法。

你们有什么建议或指点吗？

- Doug Coats

Answer 1

我认为最快的方法是将结果直接转储到表格中，使用以下代码：

Sheet1.Range("A1").CopyFromRecordset rst

然后将该范围的转储结果存储到数组中。如果它不像你喜欢的那样垂直或水平，快速复制/粘贴-特殊转置将使其快速处理，然后再将其带回数组中。

我只是建议这样做，因为您的记录集似乎相当大（2x10000），所以像您正在做的那样迭代将是耗时的，而将结果转储到工作表中，进行操作，并重新获取它们应该非常快。

更新（多年以后）。看起来可以转储到数组中。类似于：

Dim arr
rst.MoveFirst
arr = rst.GetRows

这将允许在数据传输到工作簿之前，以编程方式操作记录集（在数组中）。

- JNevill

---

我以前一直在做这个，现在进入了试验阶段，尝试提高性能，但你让它听起来像是"半斤八两"，好像权衡利弊并没有多大意义。 - Doug Coats

将记录集转储到工作表中，进行操作，然后再将其拾取回数组以执行其他操作，这种方法似乎有些笨拙，但是如果你的结果集很大，并且需要在范围内进行数据转换，那么Excel就非常擅长处理这种情况。所以我建议你让它自己去做。 - JNevill

我使用ADODB，因为我连接到Teradata（有时是SQLServer，偶尔还会连接到Excel表格），这是我感到舒适的方式。它快速、得到良好支持，并且感觉更像是一个标准，而不是通过vba查询Access中的Access。我同意我们的代码看起来非常相似。我只是想在这里提供一个示例，显示将记录集放入工作表中，进行操作，然后将其推入数组中。这是其中一些有趣的示例，有很多方法可以完成需要完成的工作：） - JNevill

@JohnShaw 自从我使用ADODB记录集工作以来已经过了很多年，尽管我认为我仍然在我的组织中有一些工作簿。您是否有将结果集转储到VBA数组的可靠方法？如果是这样，我认为它会成为回答此问题的好方法。我同意，在将其转储到工作表范围后进行操作肯定感觉不对，但我从未找到将ADODB.resultset对象转换为数组、集合或字典的更好方法。我必须想到有一种方法可以在不迭代结果集的情况下完成它。 - JNevill

@JohnShaw 我在网上查了一些资料，发现使用 ADODB.Recordset 对象的 .GetRows() 方法将数据转储到数组中似乎是一个可行的方法。由于我今天在使用 Mac，所以还没有进行测试，但感觉应该可以。我已经更新了答案以包含此方法。 - JNevill

Answer 2

虽然来晚了五年，不过这个答案应该可以回答您的问题。将Recordset转换为Array再转换为Worksheet应该是可行的。

ReDim Preserve只能用于调整上一个维度的大小，但是您还没有第1个维度，所以不能使用ReDim Preserve。

'Goes on Top
Option Explicit
Option Compare Text
Option Base 1

Public Sub Recordset_to_Array_to_Worksheet()

Dim MyArray() As Variant 'unbound Array with no definite dimensions'
Dim db as DAO.Database
Dim rst as DAO.Recordset
Dim strSQL as String, Fieldname as String
Dim i as Integer, j as Integer, colcnt as Integer, rowcnt as Integer
Dim wb as Workbook
Dim ws as Worksheet
Dim Dest as Range

'------------------------RECORDSET------------------------'
Set db = Opendatabase("URL link") 'or Set db = Currentdb()
strSQL = "SQL Statement Here"

Set rst = db.OpenRecordset(strsQL, dbOpenDynaset)

If rst.recordcount <> 0 then '///Do NOT Use "Do While Not rst.EOF" Can cause Problems///'
    colcnt = rst.Fields.Count-1
    rowcnt = rst.recordcount
 Else
    Exit Sub
End IF

'-----------------------------WRITE RECORDSET TO MYARRAY----------------------------'
ReDim MyArray (rowcnt, colcnt) 'Redimension MyArray parameters to fit the SQL returned'
rst.MoveFirst

'Populating Array with Headers from Recordset'
For j = 0 To colcnt
     MyArray(0,j) = rst.Fields(j).name
Next

'Populating Array with Record Data
For i = 1 to rowcnt
    For j = 0 to colcnt
        MyArray(i,j) = rst(j)
    Next J
    rst.movenext
Next i

'---------------------------------WORKSHEET OUTPUT---------------------------------'
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Insert Worksheet Name")
Set Dest = ws.Range("A1") 'Destination Cell
Dest.Resize(UBound(MyArray, 1) + 1, UBound(MyArray, 2) + 1).value = 
Application.Transpose(MyArray) 'Resize (secret sauce)

End Sub

- John Shaw

---

很多时候，记录集需要在发布到工作表的半永久性之前被提取到一个数组框架中，因为需要进一步转换或操作数据。以前我的一些例子包括：为返回的数据构建30、60、90、120天的老化分组；将几十个银行账户的金额报告到特定类别中；或者创建一个数组来根据时间报告金额。所有这些都需要在工作表输出之前完成。这是一个很好的问题。 - John Shaw

很高兴看到经过这么多年，这个问题仍然会偶尔受到关注。我也喜欢看看当时的自己和我的成长历程，非常酷！尽管如此，我不记得我当时在做什么项目了LOL。 - Doug Coats

@DougCoats - 我和你一样。回头看看自己走过的路程真的感觉很好。虽然我不认识你，但我仍然为你感到骄傲。 - John Shaw

这是问题的一个很好的补充。看起来需要通过记录集对象进行迭代以填充数组。我怀疑在某个点上，记录集的大小会使这比转储到工作表然后将范围转换回VBA数组以进一步操作更慢。转储到隐藏工作表，转换为数组，清除背景工作表，然后进行操作可能更快。 - JNevill

现在我写VBA的时候（我不像以前那样经常写了，现在大部分时间都被困在SQL Server里哈哈），数组几乎是默认行为。实际上，我会说与特定应用程序的对象模型交互是VBA编程中不必要的基本错误。不过，当我最初提出这个问题时，我没有完全意识到getrows()默认转置数组，我仍然不知道为什么（我认为这是为了更容易地编写新记录）。 - Doug Coats