Define an array of field names the elegant way?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
Brendan said:
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?
Click to expand...

It could be a simple case of getting the column names of the table e.g.


Dim avntTest
Dim lngLoop As Long
Dim con As ADODB.Connection

Set con = New ADODB.Connection
con.Open CONN_STRING_TO_FOXPRO
avntTest = con.OpenSchema(adSchemaColumns, _
Array(Empty, Empty, FOXPRO_TABLE_NAME, Empty)) _
.GetRows(, , "COLUMN_NAME")

For lngLoop = 0 To UBound(avntTest, 2)
Debug.Print avntTest(0, lngLoop)
Next

Jamie.

--
 
Back
Top