Storing field names from a query table into a dynamic array MS Access

Question

I have a Query qryRuleSets which outputs a table with 19 fields (that I do not want to save into an access table before that is suggested). I would like to get the field names and store them into an array so I can use that array in a for loop later on.

To find the number of fields that in the query result (to use in for loop later on) I have implemented the following, where the number of fields is stored in the variable numberfields -

numberfields = CurrentDb.QueryDefs("qryrulesets").Fields.Count

To actually get the name of these fields and store them in an array I am running into 2 problems:

1. Getting the field names from the query result

2. Setting up a dynamic array so that if the query ends up returning a table with more or less than 19 fields, it will still work

For my first problem:

I have tried to follow the steps in the following link: Get Column name from a query table but I can't figure it out.

To get the field names from the qry result I have tried the following but I'm not overly knowledgeable in vba/access so finding it hard to understand, even after a whole lot of googling:

Dim qry As QueryDef
Dim fieldNames As QueryDef
Dim firstcol As String
    
Set fieldNames = CurrentDb.CreateQueryDef(qry.qryrulesets)

firstcol = fieldNames.field(0).Name

For my second problem:

To store values in an array I have tried the following (as a test) and it works but I have to define the size of the array. Is there a way where it can be dynamic, i.e based on the value of the number of fields (found above stored in numberfields) :

Dim vardata(30) As Variant

For i = 1 To numberfields
    vardata(i) = "hello"
Next i

I tried making the '30' above to a variable value but it didn't like that.

Any and all help will be appreciated. Thanks!

Yeah but when I redim I have to specify a length right? @braX — pewpew, Aug 12 '20 at 07:48
yes, but you can redim it as many times as you want using `ReDim Preserve` - have you read the documentation for redim? https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/redim-statement — braX, Aug 12 '20 at 08:19
Obviously not very thoroughly! Thanks for that, I thought you had to specify a length manually every time - not the case. Thanks heaps! @braX — pewpew, Aug 13 '20 at 00:50
Replace the array with a scripting.dictionary. use the .count method to provide a key. — freeflow, Aug 13 '20 at 12:16

score 1 · Accepted Answer · answered Aug 12 '20 at 08:10

You can do like this:

Public Function GetFieldNames(ByVal QueryName As String) As String()

    Dim Query           As DAO.QueryDef
    
    Dim FieldNames()    As String
    Dim Index           As Integer
    
    Set Query = CurrentDb.QueryDefs(QueryName)
    
    ReDim FieldNames(0 To Query.Fields.Count - 1)
    For Index = LBound(FieldNames) To UBound(FieldNames)
        FieldNames(Index) = Query.Fields(Index).Name
    Next
    
    GetFieldNames = FieldNames()
    
End Function

Storing field names from a query table into a dynamic array MS Access

1 Answers1