ms-access - 如何在 VBA 中创建字典集合？

Question

我正在尝试在 VBA 中建立一个字典集合。集合构建完成后，将转换为 JSON，并在 HTTP 请求中发送到 Web 服务。

因为字典是对象，所以它们是通过引用而不是值添加到集合中的。结果是我的收藏由相同的词典组成，而不是我想要的单个词典。

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("qryTutors")
Set rs = qdf.OpenRecordset

Dim tutors As New Collection

If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF = True
        Dim tutor As New Scripting.Dictionary
        tutor.Add "TutorName", rs!TutorFirstName.Value & " " & rs!TutorSurname.Value
        tutor.Add "TutorEmail", rs!TutorEmail.Value
        tutor.Add "TutorSubject", rs!TutorSubject.Value
        tutors.Add tutor
        tutor.RemoveAll
        rs.MoveNext
    Loop
End If

txtOutput.Value = JsonConverter.ConvertToJson(tutors)

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up

感谢任何帮助，谢谢。

Answer 1

在每次迭代中使用一个新字典:

Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset

Set qdf = CurrentDb.QueryDefs("qryTutors")
Set rs = qdf.OpenRecordset

Dim tutors As New Collection
Dim tutor As Scripting.Dictionary

If Not (rs.EOF And rs.BOF) Then
    Do Until rs.EOF = True
        Set tutor = New Scripting.Dictionary
        tutor.Add "TutorName", rs!TutorFirstName.Value & " " & rs!TutorSurname.Value
        tutor.Add "TutorEmail", rs!TutorEmail.Value
        tutor.Add "TutorSubject", rs!TutorSubject.Value
        tutors.Add tutor
        rs.MoveNext
    Loop
End If

txtOutput.Value = JsonConverter.ConvertToJson(tutors)

rs.Close 'Close the recordset
Set rs = Nothing 'Clean up