Getting tables's fields name

[Maracay]

Hi guys

How can I know 1.- The number of columns in a table and 2.- The field names
of a query or table.

Thanks

 

[Clifford Bass]

Hi,

You could inspect the Fields object of either the
CurrentDb.QueryDefs("YourQueryName") or CurrentDb.TableDefs("YourTableName")
objects. For example:

Dim db As DAO.Database
Dim fld As DAO.Field
Dim qd As DAO.QueryDef
Dim td As DAO.TableDef

Set db = CurrentDb
Set qd = db.QueryDefs("Query1")
MsgBox qd.Name & " has " & qd.Fields.Count & " fields."
For Each fld In qd.Fields
MsgBox fld.Name
Next fld
Set td = db.TableDefs("Table1")
MsgBox td.Name & " has " & td.Fields.Count & " fields."
For Each fld In td.Fields
MsgBox fld.Name
Next fld

Hope that helps,

Clifford Bass

 

[fredg]

Hi guys

How can I know 1.- The number of columns in a table and 2.- The field names
of a query or table.

Thanks

Why?
Several ways, depending upon what you wish to do with the information.

Here is one:

Public Sub CountFields()
Dim db As DAO.Database
Dim tbl As DAO.TableDef
Dim qdf As DAO.QueryDef

Set db = CurrentDb
For Each tbl In db.TableDefs
If Not Left(tbl.Name, 4) = "MSys" Then
Debug.Print tbl.Name,
Debug.Print " Fields count " & tbl.Fields.Count
End If
Next tbl

For Each qdf In db.QueryDefs
If Left(qdf.Name, 1) <> "~" Then
Debug.Print qdf.Name,
Debug.Print " Fields count " & qdf.Fields.Count
End If

Next qdf

End Sub