MS ACCESS 2007 VBA : DAO recordset....how can I view all the "fields" in the returned collection

Question

so if i do a SQL statement like so:

sql = "SELECT * FROM tblMain"

     set rs = currentdb.openrecordset(sql)

what method can i use to view every "field name" in this collection i have just created. i am getting some very strange error stating that the item is not found in this collection.

i know the field exists in the table, i have triple checked the spelling everywhere when i reference it, and the SQL should be pulling everything, but i want to see it.

is there a debug.print method to see all these fields

thanks Justin

Answer 1

You can iterate through the fields collection of the recordset.

Code is OTTOMH

Dim NumFields as Integer

For NumFields = 0 to rs.Fields.Count -1
    Debug.Print Rs.Fields(NumFields).Name
Next

Alternately, you can set a breakpoint at set rs = currentdb.openrecordset(sql) and then as soon as the statement executes, right-click on rs, choose add watch and view the whole thing in the Watches window.

Answer 2

Here is a script that will look for a field containing the string you specify in every table in an Access database (except System and Attached Tables) and write it to text files:

Option Compare Database
Option Explicit

Sub main()
Dim db As Database
Dim rs As Recordset
Dim bFinished As Boolean
Dim sFieldName As String
Dim iPosition, z, x As Integer
Dim bRetVal  As Boolean
Dim tdTemp As TableDef
Dim iDatabaseNumbers As Integer


Const FIELD_TO_FIND = "FieldName"


Set db = CurrentDb

Open Left(db.Name, Len(db.Name) - 4) & "_" & FIELD_TO_FIND & ".txt" For Output As #1

    For x = 0 To db.TableDefs.Count - 1

        Set tdTemp = db.TableDefs(x)
        bRetVal = IIf(tdTemp.Attributes And dbSystemObject, False, True)
        If bRetVal Then
            bRetVal = IIf(tdTemp.Attributes And dbAttachedTable, False, True)
        End If

        If bRetVal Then
            Set rs = db.OpenRecordset(db.TableDefs(x).Name)
                If rs.RecordCount > 0 Then
                    For z = 0 To rs.Fields.Count - 1

                        sFieldName = rs.Fields(z).Name
                        If InStr(1, sFieldName, FIELD_TO_FIND, vbTextCompare) > 0 Then
                            Print #1, db.TableDefs(x).Name
                            Exit For
                        End If
                    Next z
                End If
        End If
    Next x

Close #1

MsgBox "Done"

End Sub

You could adjust accordingly to make it do what you need.

Answer 3

This is a variation on the other answers, but I believe it's better to use a For/Each loop than a counter:

  Dim rs As DAO.Recordset
  Dim fld As DAO.Field

  Set rs = CurrentDB.OpenRecordset("SELECT * FROM tblMain")
  For Each fld In rs.Fields
    Debug.Print fld.Name
  Next fld
  Set fld = Nothing
  rs.Close
  Set rs = Nothing