How to get the list of fields in a table with VBA

[SaigonL]

This is probably very simple to answer.

I can't find the right way to write the code.
I tried this but won't work:

For Each Field In Tables!MyTable.Fields

Debug.Print Field.Name

Next

Can anyone help?

 

[PHV]

You have to play with the Fields collection of the TableDef object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[fdcusa]

Do you want to just display in a form, or save the information to a table? There are several examples here - search the site for "list fields in tables", or something to that effect... You may also want to peruse my thread where I am attempting to enumerate ALL objects in a 1800-odd primary objects database (

http://www.tek-tips.com/viewthread.cfm?qid=1279221&page=1).

Hope that helps.

 

[SaigonL]

Thanks PHV and fdcusa,

I still cannot find the right syntax to cycle through all field names in my table.

PHV, would you have an example to suggest with the TableDef and Fields collections?

 

[formerTexan]

Try the following

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim clm As ADOX.Column
Dim idx As ADOX.index
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
        rs.Open (tbl.Name), cnn, adOpenForwardOnly, adLockReadOnly

        Do While Not rs.EOF
            For Each fld In rs.Fields
                    With fld
		‘DO WHATEVER
                    End With
            Next
        rs.MoveNext
        Loop
        rs.Close

cnn.Close
Set rs = Nothing
Set cnn = Nothing

Cheers,
Bill

 

[missinglinq]

What version of Access are you running? You've indicated you just want to see the fields in a table, why not use the Documenter?

Tools-> Analyze-> Documenter

Then select Tables and check the ones you're interested in.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[PHV]

PHV, would you have an example to suggest with the TableDef and Fields collections?
A starting point:
Sub ListFieldNames(strTableName As String)
Dim Dbs As Database
Dim Table_Definition As TableDef
Dim fldLoop As DAO.Field
Set Dbs = CurrentDb
Set Table_Definition = Dbs.TableDefs(strTableName)
For Each fldLoop In Table_Definition.Fields
Debug.Print fldLoop.Name
Next
End Sub

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SaigonL]

Ok, thanks FormerTexan, missinglinq, and PHV for your suggestions.

I'll have a try.