How do I loop through table columns without using column names?

[tmj00345]

I have imported an Excel worksheet as an Access table. Access assigns the
column names (Field1, Field2, ...... Fieldn).

I want to read a row, loop through those columns, read the next row, etc.

So it's a "Do Until no more columns" nested inside a "Do until no more rows".

The reason I need to do this is that each day I will import a new version of
the worksheet, overwriting the table I import to. From day to day, there will
be a different number of rows (no problem there) and also a different number
of columns (aye, there's the rub!)

Any ideas how to do this?

 

[Allen Browne]

Something like this:


Function ShowRecordsAndFields()
Dim rs As DAO.Recordset
Dim fld As DAO.Field

Set rs = DBEngine(0)(0).OpenRecordset("Table1")
Do While Not rs.EOF
For Each fld In rs.Fields
Debug.Print fld.Name & " = "; rs(fld.Name),
Next
Debug.Print
rs.MoveNext
Loop
rs.Close
End Function

 

[Rick Brandt]

I have imported an Excel worksheet as an Access table. Access assigns
the column names (Field1, Field2, ...... Fieldn).

I want to read a row, loop through those columns, read the next row,
etc.

So it's a "Do Until no more columns" nested inside a "Do until no
more rows".

The reason I need to do this is that each day I will import a new
version of the worksheet, overwriting the table I import to. From day
to day, there will be a different number of rows (no problem there)
and also a different number of columns (aye, there's the rub!)

Any ideas how to do this?

Your Recordset has a Fields collection so you can use...

Dim fld as Field

For Each fld in rs.Fields
'do something
Next fld