Scripting.Dictionary Object (1 Viewer)

[DanR]

Hello

I'm using a dictionary object for the first time, and it's behaving rather weirdly. The aim is to add all of the records from a 2 column recordset into the dictionary:

Here's the code:

rstActual.Open strSQL, cnCurrentDb, adOpenStatic, adLockReadOnly
Do Until rstActual.EOF

dict.Add rstActual!SPUD, rstActual!TaxonCount
rstActual.MoveNext
​

Loop

There are about 9000 records in the recordset, but I get an error on the 2nd record: Error 457 - This key is already associated with an element of this collection.

But the key for the first element (rstActual!SPUD) is 1. For the second element it's 4, so it shouldn't be a duplicate key. I've checked the behaviour carefully, and it is weird.

Any ideas about what the problem could be?

 

[fuzzygeek]

This site gave me the clue to what is going on and is a good explanation. You must load the value of the recordset.

http://www.codecomments.com/ASP/message787952.html

This code worked:

Function TestCode()
Dim rstActual As ADODB.Recordset
Dim cnCurrentdb As ADODB.Connection
Dim strSql As String
Dim dict
Dim t As Integer, d, a, i
Set dict = CreateObject("Scripting.dictionary")


strSql = "select * from tblTest;"
Set rstActual = New ADODB.Recordset
Set cnCurrentdb = CurrentProject.Connection


rstActual.Open strSql, cnCurrentdb, adOpenStatic, adLockReadOnly

Do Until rstActual.EOF

dict.Add rstActual!Key.Value, rstActual!Value.Value

rstActual.MoveNext

Loop
a = dict.Items 'Get the items

For i = 0 To dict.Count - 1 'Iterate the array
Debug.Print a(i) 'Print item
Next
End Function

 

[DanR]

Thanks very much for your research, especially that other link. A big help - especially when you can go to sleep mulling over a problem, and wake up to the answer

cheers
Dan