Variable variables for array function error 2482 1

[wendyp]

I can't figure out if this is possible in Access. I am getting Run-time error 2482 can't find the name "test1" you entered in the expression.

I am sending 2 arrays to a function that executes an SQL INSERT procedure. One array contains the table field names and the other the data. If I could have an associative array, I wouldn't have a problem. But since I don't, I have to make sure the data is in the correct spot in the array.

Since I have to match the field names to the data, I would like to do:

arrFields = array("field1", "field2")
arrData = array(tmp1, tmp2)

The problem comes in that throughout the procedure, I need to execute the SQL insert procedure multiple times and the tmpX data is changing. So currently I just reexecute the arrData = array() line. But this gets messy and tricky when you have 20 variables that are changing frequently during development.

So I want one place to tell it what the variable names are, but not actually evaluate the variables until I've assigned them.

Public Function test()

    Dim arrData As Variant

    Dim arrVariables As String

    arrVariables = "test1, test2, test3"

    Dim test1 As String
    Dim test2 As String
    Dim test3 As String
        
    test1 = "Hello"
    test2 = "I"
    test3 = "did it"
      
    arrData = Array(Eval(arrVariables))

    Dim i As Integer

    For i = 0 To UBound(arrData)

        Debug.Print arrData(i)

    Next

End Function

Is there some function that I'm not thinking of - or some way to use Eval in this context?

/Wendy

 

[Remou]

If you need an associative array, have you considered the Dictionary Object?

[tt]Dictionary Object
Object that stores data key, item pairs.

Remarks
A Dictionary object is the equivalent of a PERL associative array. Items, which can be any form of data, are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually a integer or a string, but can be anything except an array.[/tt]

From:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbenlr98/html/vaobjDictionary.asp

 

[wendyp]

That looks like it might do the trick. What library is it in? I just tried Microsoft Scripting Library, but although it can find "Scripting" as an object type - it can't find "Dictionary" as a method or member.

/Wendy

 

[Remou]

The library is the Microsoft Scripting Runtime. I have just noticed that there is an error in the Microsoft article I posted above:

[tt]Dim d As Scripting.dictionary
Set d = CreateObject[blue]("Scripting.Dictionary")[/blue]
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"
Debug.Print d("b")[/tt]

 

[wendyp]

Beautiful! That seems to be exactly what I needed. You get a star - especially for first thing Monday morning!

Thanks,
/Wendy