Array problem

[Jerome]

Hello,

I've got this code:

Dim FieldNames() As String
Dim Rst As Recordset
Dim f As Field
Dim Counter As Integer
    
Set Rst = CurrentDb.OpenRecordset("TblParts")
    
Counter = 0
    
    For Each f In Rst.Fields
    FieldNames(Counter) = f.Name
    Counter = Counter + 1
    Next
    Rst.Close

ReDim Preserve FieldNames(Counter)

I still get the error subscript out of range (error 9). In the help file there is this:
Dim MyArray() As Integer
MyArray(8) = 234 ' Causes Error 9.
This is because I've declared an array without a dimension (i think )

How to declare a array without knowing how much elements will eventually be in it?

Thanks in advance

 

[jjturner]

It's been awhile since I've wrestled with arrays, but I believe with an empty array, it simply assumes myArray(0) - meaning only 1 value can be stored in it.

Unless and until you ReDim, you can't be attempting to assign any further values to the array.

So, I would recommend:

Dim FieldNames() As String
Dim Rst As Recordset
Dim f As Field
Dim iNumFields As Integer
Dim Counter As Integer
    
Set Rst = CurrentDb.OpenRecordset("TblParts")

With Rst   
    iNumFields = .Fields.Count
    ReDim FieldNames(0 to iNumFields - 1) 'using base-0 to match with defaults
    
    For Each f In .Fields
        FieldNames(Counter) = f.Name
        Counter = Counter + 1
    Next
    .Close
End With

Again, I'm a bit rusty with array usage and I don't have Access available to test with at the moment, but hopefully you see the logic of setting the proper dimensions prior to writing values into the array...

Cheers,
John

 

[Jerome]

Your code makes sense to me. I was struggling how to set the dimension of the array before the For Each is started.

BTW: Is there also another way to get the field/column names (without opening the record set) into a array?

Thanks.

 

[jjturner]

Good question -

In fact, using recordset is overkill and potentially adds an unwarranted amount of 'overhead' (although it would be interesting to test what performance impact it actually has)

To bypass the recordset, and go straight to structure look up the TableDef object.

Cheers,
John