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?

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"tmj00345" <(E-Mail Removed)> wrote in message
news:8F72EBC7-001E-4683-9D0E-(E-Mail Removed)...
>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?
>

tmj00345 wrote:
> 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



--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com