How to get a list of fields from a table in Access

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Howdy All,

I can use the system table "MSysObjects" to give me a list
of tables in an Access database, is there a way to get a
list of fields from tables in the database? Is there
another hiddent table?

Best,

Kevin
 
I can use the system table "MSysObjects" to give me a list
of tables in an Access database, is there a way to get a
list of fields from tables in the database? Is there
another hiddent table?
Click to expand...

No other hidden table ;-)

Dim rst As Recordset
Dim fld As Field
Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)
For Each fld In rst.Fields
Debug.Print fld.Name & " " & fld.Type 'or whatever
Next
rst.Close
Set rst = Nothing

If you need a field list in a combobox just use "field list" as rowsource.

HTH - Peter
 
Peter Doering said:
No other hidden table ;-)

Dim rst As Recordset
Dim fld As Field
Set rst = CurrentDb.OpenRecordset("YourTable", dbOpenDynaset)
For Each fld In rst.Fields
Debug.Print fld.Name & " " & fld.Type 'or whatever
Next
rst.Close
Set rst = Nothing

If you need a field list in a combobox just use "field list" as
rowsource.
Click to expand...

Or, alternatively, you can avoid opening a recordset and just examine
the tabledef (assuming it's a local table):

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = CurrentDb
Set tdf = db.TableDefs("YourTable")

For Each fld In tdf.Fields
Debug.Print fld.Name, fld.Type
Next fld

Set tdf = Nothing
Set db = Nothing
 
Back
Top