How to Use a Multidimensional Array to Execute Multiple Queries

[gicarto]

Hello-

I have a query that I want to run multiple times using a multidimensional array to feed it the values. As you can see, I have created a two column static array with the values that need to be executed in each query. In the SQL statement below, the tags in red is where I want to insert the values from each row of the array. I can't seem to figure out the notation for inside the SQL statement. There are plenty of examples online for a one-dimensional array but there isn't much for larger arrays.

Private Sub Command0_Click()


    Dim i, j As Long
    Dim arr(0 To 6, 0 To 1) As String
        arr(0, 0) = "repair":       arr(0, 1) = "3"
        arr(1, 0) = "missing":      arr(1, 1) = "3"
        arr(2, 0) = "structure":    arr(2, 1) = "6"
        arr(3, 0) = "missing":      arr(3, 1) = "9"
        arr(4, 0) = "missing":      arr(4, 1) = "12"
        arr(5, 0) = "repair":       arr(5, 1) = "15"
        arr(6, 0) = "repair":       arr(6, 1) = "16"
        
For i = 0 to 6
For j = 0 to 1
    SQL = "UPDATE parcel INNER JOIN conditions ON " & _
            "parcel.ID = conditions.parcel_id SET conditions." & _
            " {array(1)} = 99 " & _ ' example "repair" arr(0,0)
            "WHERE conditions.factor_id = {array(2)}) And parcel.structure = False " ' example "3" arr(0,1)
 Next j
Next i
End Sub

Thanks in advance.

[kd2017]

Kind of a shot in the dark:

        SQL = "UPDATE parcel INNER JOIN conditions ON " & _
              "parcel.ID = conditions.parcel_id SET conditions." & _
              arr(i, 0) & " = 99 " & _
              "WHERE conditions.factor_id = " & arr(j, 1) & " And parcel.structure = False "

Add

Debug.Print SQL

to print the string you've built in the immediate window (Ctrl-G) and check that it's like you wanted.

[gicarto]

I got it figured out. I removed the j loop and used the combinations of arr(i,0) and arr(i,1). Thanks for setting me in the right direction!