Access VBA collection recreate freezes code

Tin Man 1 Reputation point
2021-08-31T19:37:53.667+00:00

I have this collection defined in a class. It holds a small amount of data. It is created and populated upon initializing a program without problems.
pRefList As Collection

I refresh the collection by first destroying it with this code.
Call ClearCollection(container:=pRefList)
If Not pRefList Is Nothing Then
Set pRefList = Nothing
End If
Set pRefList = New Collection
/- Next, I repopulate it with data

Public Sub ClearCollection(ByRef container As Collection)
If Not container Is Nothing Then
For index = 1 To container.count
' container.item(index) = Nothing
container.Remove 1
Next

There is probably "over-kill" here as I am just trying whatever I can to make it work. Regardless, my computer seems to enter periodically into a state about 10% of the time where trying to destroy this collection causes the entire instance to freeze. Oddly, if I run a much longer program that ends with recreating this collection, it works every time. Many times, if I break on this part of the code and step through slowly, it works. It seems to have problems releasing whatever references are associated.

How can I best repopulate this collection without getting this problem?

Access Development
Access Development
Access: A family of Microsoft relational database management systems designed for ease of use.Development: The process of researching, productizing, and refining new or existing technologies.
810 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Tom van Stiphout 1,616 Reputation points MVP
    2021-09-01T03:43:52.97+00:00

    I'm not sure about this SPECIFIC situation, but in general if we want to delete items from a collection we want to do it from top to bottom, not from bottom to top.

    For index = container.count to 1 step -1

    0 comments No comments