How can I make a collection of dictionaries in VBA?

Question

I'm trying to build up a collection of dictionaries in VBA. After the collection has been built, it will be converted to JSON, and sent to a web service in a HTTP request.

Because dictionaries are objects, they are added to the collection by reference, not by value. The result is that my collection is made up of identical dictionaries, rather than the individual dictionaries that I wanted.

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

Any help appreciated, thanks.

Answer 1

Use a new dictionary in each iteration:

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