ArrayList in MS Access without .NET 3.5?-CodePudding

I need to create an ArrayList in MS Access and then add records to it. I'm using following method:

Dim orphaned As Object
Set orphaned = CreateObject("System.Collections.ArrayList")

Unfortunately this method requires installing .NET 3.5 which I'd like to avoid.

I'm rather inexperienced with VBA and Access. Is there any workaround/alternative?

CodePudding user response：

Hum, why not use the VBA collection object? They are fast, and easy to use.

  Sub TestFun55()
  
     Dim MyThings As New Collection
     
     ' add 5 things to above
     Dim strThing      As String
     Dim i             As Integer
     
     For i = 1 To 5
     
        Dim strKey     As String
        strKey = i * 10
     
        strThing = "Thing - " & strKey
        MyThings.Add strThing, strKey
        
     Next i
     
     ' get one thing by "key" value (we have 10, 20, 30, 40, 50
     
     Debug.Print "Get first thing thing by index (1) = " & MyThings(1)
     
     Debug.Print "Get thing by 'key value '10' (string key) = " & MyThings("10")
     
           
  End Sub

So, you can fetch out of the collection by "index" (starts at 1).

Or you can fetch by a "key". Now for a strings, then I tend to have both "key" and the string value the same. But they are VERY fast performance wise to find/get a value by string.

So, output from above is this:

  Get first thing thing by index (1) = Thing - 10
  Get thing by 'key value '10' (string key) = Thing - 10

And often, if you want strong typed vars, and a easy intel-sense?

Well, for SOME reason, VBA does not support type defs.

so, in 100% separate -- create a "class" module with this:

' module = clsPerson
  Option Compare Database
  Option Explicit
        
  Public FirstName As String      
  Public LastName As String

Now, we can go like this:

        Sub TestFun56()
  
     Dim MyThings As New Collection
     ' add 3 Person things to colleciton
     
     Dim OnePerson As clsPerson
              
     Dim strThing      As String
     Dim i             As Integer
     
     For i = 1 To 2
     
        Set OnePerson = New clsPerson
        OnePerson.FirstName = "First" & i
        OnePerson.LastName = "Last" & i
        
        Dim strKey     As String
        strKey = "First" & i      ' we key list by FirstName
        Debug.Print "key = " & strKey
        
        MyThings.Add OnePerson, strKey
        
     Next i
     
     ' now, check for first name of "First2" in list
     
     On Error Resume Next       ' failure to find generates error!!!
     
     Dim strFind As String
     Dim strResult As clsPerson
     
     
     strFind = "First1"
     Set strResult = MyThings(strFind)
     
     If Err.Number = 0 Then
        ' got one!
        Debug.Print strResult.FirstName
        Debug.Print strResult.LastName
     Else
        Debug.Print "not found"
     End If
     
     
     ' and of course we can referace/use by index
     
     Debug.Print MyThings(1).FirstName
     Debug.Print MyThings(1).LastName
     
           
  End Sub

Regardless? I collection is nice. You also have

 MyThings.Count - how many
 MyThings(int index).

Unfortantly, here is no way to convert a string "key" to a index.