We help IT Professionals succeed at work.

Table and Field Collection

APD Toronto
APD Toronto asked
Hi Experts,

In MS Access how can I access the table collection, and then the field collection for each table. I am essentially looking to create a For Each loop.

Thank you
Watch Question

SimonPrincipal Analyst
Here's a very simple loop to iterate thru each field in each table...
Sub ListTables()
Dim db As dao.Database
Dim tbl As dao.TableDef
Dim fld As dao.Field
Dim attrib As String

Set db = CurrentDb
For Each tbl In db.TableDefs
    attrib = (tbl.Attributes And dbSystemObject)
    Debug.Print tbl.Name & IIf(attrib, ": System Table", ": Not System Table")
    For Each fld In tbl.Fields
        Debug.Print vbTab & fld.Name
    Next fld
Next tbl
End Sub

Open in new window

Top Expert 2016
you need to define them first

dim td as dao.tabledef, fld as dao.field, db as dao.database
set db=currentdb

for each td in db.tabledefs
      for each fld in td.fields
            debug.print td.name & " > " & fld.name