VBA array dynamically sized

I have a function whereby I am trying to create an insert into statement by saving the column names from a select and using that output to write the insert into. So I try to create a 2- dimensional array filling the first slot with the areas (651,652 and 801) and the 2nd slot with the column name, which I can get from a select.

So my function starts like this:

Function create_insert() as boolean

Dim arrCol(0 to 2, 0 to 75) as variant

Dim A(0 to 2) as variant

A(0) = "65"
A(1) = "66"
A(2) = "80"

Set dbs=currentdb()

For i = lbound(A) to 2

Set rs= dbs.openrecordset("select...". & A(i))

Do while not rs.eof

  ArrCol(i,0) = A(i)
  ArrCol(i,i+j)=rs(0).Value
  J=j +1
  rs.movenext
Loop

Next i

But I'm having problems with the dimensions of the array. As I would like it more dynamic instead of the 75 I have statically defined it as.

By the way it's 3 tables (areas-65,66,80) that I'm getting the columns from, so I need both information,the table and column.

I also have a general table called "creator", where each field is a row and a column called area, which is obviously either 65,66 or 80.. The areas.

Solution

Unless Function returns a value or needs to be called by macro, could just be a Sub.

Declare array without dimensions then Redim based on recordset Recordcount.

Sub create_insert()

Dim arrCol() As Variant
Dim A(0 To 2) As Variant
Dim dbs As DAO.Database, rs As DAO.Recordset, i As Integer, j As Integer

A(0) = "651"
A(1) = "652"
A(2) = "801"

Set dbs = CurrentDb()

For i = 0 To 2
    Set rs = dbs.OpenRecordset("SELECT * FROM [" & A(i) & "]")
    rs.MoveLast
    rs.MoveFirst
    ReDim arrCol(3, rs.RecordCount)
    j = 1
    Do While Not rs.EOF
        arrCol(i, 0) = A(i)
        arrCol(i, j) = rs(0).Value
        Debug.Print arrCol(i, 0), arrCol(i, j)
        j = j + 1
        rs.MoveNext
    Loop
Next i
End Sub