Create One Dimensional Dynamic Array

Question

How can I add field names to an array, when I do not know the size of the array? In my two functions below, I am able to populate the table no problem, but when I try to create the array, it will only print the 1st record from the table. What must I do in order to create the array with all values?

Function PopulateTable()
Set rs1 = db.OpenRecordset("MasterList")
For Each fld In rs1.Fields
  StrSQL = "INSERT INTO HoldTable (FieldList) VALUES ('" & fld.Name & "' );"
  DoCmd.SetWarnings False
  DoCmd.RunSQL StrSQL
Next
'Create array of all fld.Name Values
PopulateArray
End Function

Function PopulateArray()
Dim rstData    As DAO.Recordset
Dim v          As Variant
Dim cn         As Variant

Set rstData = CurrentDb.OpenRecordset("select fieldlist from HoldTable")
v = rstData.GetRows(rstData.RecordCount)
For Each cn In v
  Debug.Print CStr(cn)
Next

End Function

From the documentation (my emphasis), "GetRows returns a two-dimensional array." — Comintern
– Comintern, Commented Dec 15, 2016 at 2:02

user6432984 · Accepted Answer · 2016-12-15 02:08:34Z

1

You should use ADODB.Connection and OpenSchema method to get a list of field names: List database objects (tables, columns..) using ADO/ADOX

You'll need to use rstData.MoveLast and then rstData.MoveFirst to get an accurate RecordCount.

Function PopulateTable()
    Set rs1 = CurrentDb.OpenRecordset("MasterList")
    For Each fld In rs1.Fields
        StrSQL = "INSERT INTO HoldTable (FieldList) VALUES ('" & fld.Name & "' );"
        'Create array of all fld.Name Values
        DoCmd.SetWarnings False
        DoCmd.RunSQL StrSQL
    Next
    PopulateArray
End Function

Function PopulateArray()
    Dim rstData As DAO.Recordset
    Dim v As Variant
    Dim cn As Variant

    Set rstData = CurrentDb.OpenRecordset("Select fieldlist FROM HoldTable")
    rstData.MoveLast
    rstData.MoveFirst
    v = rstData.GetRows(rstData.RecordCount)
    For Each cn In v
        Debug.Print CStr(cn)
    Next

End Function

answered Dec 15, 2016 at 2:08

user6432984

Sign up to request clarification or add additional context in comments.

2 Comments

StarsFlyFree FromCozyNights Over a year ago

Excellent, that was what I needed. Thank you for the assistance.

user6432984 Over a year ago

@StarsFlyFreeFromCozyNights Thank you for accepting my answer..again. :)

CAMD_3441 · Accepted Answer · 2016-12-15 02:28:39Z

1

This is how I would create a dynamic array. I would loop through the data and within each iteration increment the size of the array and then set the value to the newly incremented array

Dim arraySize as Integer
Dim dynArray() As string
arraySize = -1 ' set to -1 so when you first increment the size by one it will start at index 0

' Iterating through rstData variable
for each fld in rstData.fields
   arraySize = arraySize + 1
   redim preserve dynArray(arraySize) as String
   dynArray(sz) = fld ' not sure if can set it to fld directly or if need to access the value property of fld
next
' End of iterating through

answered Dec 15, 2016 at 2:28

CAMD_3441

3,3024 gold badges30 silver badges44 bronze badges

Comments

Zaur Amikishiyev · Accepted Answer · 2016-12-15 02:09:00Z

0

You can define a "normal" variable and assign an empty array to it:

Dim v : v = Array()

Then you can redim it:

ReDim Preserve v(rstData.RecordCount)

answered Dec 15, 2016 at 2:09

Zaur Amikishiyev

3782 silver badges6 bronze badges

2 Comments

StarsFlyFree FromCozyNights Over a year ago

If I add the ReDim Preserve statement under my Debug.Print statement, I get an error of 'The array is fixed or temporarily locked'

Zaur Amikishiyev Over a year ago

but did you also change the declaration of v to Dim v() or Dim v : v = Array()?

Collectives™ on Stack Overflow

Create One Dimensional Dynamic Array

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related