使用集合对象将记录集中的匹配项存储在Excel VBA中

Question

使用Excel VBA，我试图在SQLite表中搜索文件名中出现的名称。

在下面的代码中，我有一个NamesFound集合对象来存储这些名称。

当我循环浏览记录集时，我可以将名称添加到NamesFound并打印它们。

关闭记录集并销毁变量后，当我打印集合中的项数(NamesFound.count(时，我会得到一个与文件名中匹配名称数相匹配的数字。

然而，当我尝试打印集合中的任何元素时，我会收到错误消息"；对象不再有效"；。

知道为什么会发生这种事吗？

Option Explicit
Sub SQLiteMatch()
Dim strSQL As String, fn As String

Dim NamesFound As Collection
Set NamesFound = New Collection

Dim conn As Object
Set conn = CreateObject("ADODB.Connection")

Dim rst As Object
Set rst = CreateObject("ADODB.Recordset")

fn = "C:Clark Gable & Vivian Leigh in Gone With The Wind.mp4"

conn.Open "DRIVER=SQLite3 ODBC Driver;Database=C:PathTocast&crew.db;"

strSQL = "SELECT id, person_name from People"

rst.Open strSQL, conn, 1, 1

With rst
.MoveFirst
Do Until .EOF
If InStr(1, fn, ![person_name]) > 0 Then
NamesFound.Add ![person_name]
Debug.Print "Names found: " & NamesFound.Count & " - " & _
NamesFound(NamesFound.Count)                                '<<< Works fine
End If
.MoveNext
Loop
End With

rst.Close

Set rst = Nothing
Set conn = Nothing

Debug.Print NamesFound(1) '<<< Error #3420: Object is no longer valid -
' same error for NamesFound.item(1) and NamesFound(1).Value
End Sub

Answer 1

也许可以试试：

...
'copy ![person_name] to a variable before adding to the Collection      Dim personNameCopy As String        With rst            .MoveFirst          Do Until .EOF
personNameCopy = ![person_name]
If InStr(1, fn, personNameCopy ) > 0 Then
NamesFound.Add personNameCopy 
Debug.Print "Names found: " & NamesFound.Count & " - " & _
NamesFound(NamesFound.Count)                                '<<< Works fine
End If
.MoveNext           Loop        End With
...

在执行最后一个Debug.Print之前，rst变量被设置为Nothing，并且可能对添加到集合的![person_name]引用产生影响。