Search code examples
excelvbaadorecordset

Looping through the ADO fields collection in VBA with an empty recordset

I have an ADO recordset that returns no rows (which is expected), but my watches panel shows a valid field collection, with column names that I want to store / capture.

However, using code like 

x = rs.Fields(idx)

returns the error '3021' : Either BOF or EOF is True. Requested operation requires a current record.

My question is is it possible to read the Fields collection (noting that the contents that I can see in the watches panel appear correct and what I want to be able to grab), and if not, what are the "gotchas" that explain why?

Many thanks Mike

Solution

  • You need name:

    x = rs.Fields(idx).Name

    Field(idx) is the value, which does not exists.