CODEWITHSUNDEEP
arraysvbams-accessms-access-2013
StarsFlyFree FromCozyNights
StarsFlyFree FromCozyNights

Reputation: 303

Create One Dimensional Dynamic Array

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

Upvotes: 1

Views: 844

Answers (3)

CAMD_3441
CAMD_3441

Reputation: 3174

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

Upvotes: 1

Zaur Amikishiyev
Zaur Amikishiyev

Reputation: 378

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)

Upvotes: 0

user6432984
user6432984

Reputation:

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

Upvotes: 1

Related Questions