Define an array of field names the elegant way?

[jsteeves]

Background
========
I am trying to copy a remote table to use locally as a buffer

1) i cannot "link" to the table due to contention issues
2) OpenLocal opens a recordset using currentproject.connection and accepts
the source as a string
3)OpenFoxPro opens a connection to the foxpro free table directory
4)OpenFoxProTables opens a recordset using the connection created by
OpenFoxPro and accepts the source as a string

Is it possible to define an array of field names without having to type them
all out? i have a bunch

What i have
========

Public Sub FillBuffer()
Dim aSource As Variant
Dim aDest As Variant

'>>>>> Open the local Buffer table
OpenLocal "SELECT * FROM buffer"

'***** Clear Buffer
If LocalRst.BOF = False Then
LocalRst.MoveFirst
End If
Do While LocalRst.EOF = False
LocalRst.Delete
LocalRst.MoveNext
Loop

'>>>>> Connect to FoxPro freetable directory
OpenFoxPro

'>>>>> Connect to vfptable
OpenFoxProTable "SELECT * FROM vfptable.dbf"

'***** BEGIN DATA TRANSFER
aDest = Array(Do I really have to type out all those fields?)
aSource = Array(Do I really have to type out all those fields?)
If FoxProRst.BOF = False then
FoxProRst.MoveFirst
End If
Do While FoxProRst.EOF = False
LocalRst.AddNew aDest, aSource
FoxProRst.MoveNext
Loop
'***** END DATA TRANSFER

'<<<<< Close local Buffer table
CloseLocal

'<<<<< Close Connection to foxpro
CloseFoxpro

'<<<<< Close Connection to foxprotable
CloseFoxProTable

End Sub

 

[Brendan Reynolds]

Do I understand correctly, you have an open recordset, and you want to fill
an array with the names of the fields in that recordset? If so, here's an
example ...

Public Sub FieldNameArray()

Dim rstTest As ADODB.Recordset
Dim astrTest() As String
Dim lngLoop As Long

Set rstTest = New ADODB.Recordset
rstTest.Open "SELECT * FROM Table1", CurrentProject.Connection
ReDim astrTest(rstTest.Fields.Count - 1)
For lngLoop = 0 To rstTest.Fields.Count - 1
astrTest(lngLoop) = rstTest.Fields(lngLoop).Name
Next lngLoop
rstTest.Close

For lngLoop = LBound(astrTest) To UBound(astrTest)
Debug.Print astrTest(lngLoop)
Next lngLoop

End Sub