Code to list Tables & Fields in a mdb

  • Thread starter Thread starter Jim Pockmire
  • Start date Start date
J

Jim Pockmire

What code can I use to list all of the tables in a database, or all of the
fields in a table?
 
Use the built in Documenter. It will provide a printout of whatever you
select.
Tools, Analyze, Documenter
 
Option Compare Database
Option Explicit

Public Sub TablesAndFields2()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim rst As DAO.Recordset
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdfs = db.TableDefs
For Each tdf In tdfs
If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And UCase$(Left$(tdf.Name, 4))
<> "USYS" Then
Debug.Print "Table Name: " & tdf.Name
Set rst = db.OpenRecordset("SELECT * FROM [" & tdf.Name & "] WHERE
False")
Set flds = rst.Fields
For Each fld In flds
Debug.Print "Field Name: " & fld.Name, "Field Type: " & fld.Type
Next fld
rst.Close
Set rst = Nothing
End If
Next tdf
db.Close

End Sub
 
Why bother with the recordset?

Option Compare Database
Option Explicit

Public Sub TablesAndFields2()

Dim db As DAO.Database
Dim tdfs As DAO.TableDefs
Dim tdf As DAO.TableDef
Dim flds As DAO.Fields
Dim fld As DAO.Field

Set db = CurrentDb()
Set tdfs = db.TableDefs
For Each tdf In tdfs
If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And _
UCase$(Left$(tdf.Name, 4)) <> "USYS" Then
Debug.Print "Table Name: " & tdf.Name
Set flds = tdf.Fields
For Each fld In flds
Debug.Print "Field Name: " & fld.Name, "Field Type: " & fld.Type
Next fld
End If
Next tdf
db.Close

End Sub

For that matter, you don't really need to instantiate some of the objects
you are. This will work just as well:

Public Sub TablesAndFields2()

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

Set db = CurrentDb()
For Each tdf In db.TableDefs
If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And _
UCase$(Left$(tdf.Name, 4)) <> "USYS" Then
Debug.Print "Table Name: " & tdf.Name
For Each fld In tdf.Fields
Debug.Print "Field Name: " & fld.Name, "Field Type: " & fld.Type
Next fld
End If
Next tdf
db.Close

Another option is to replace

If UCase$(Left$(tdf.Name, 4)) <> "MSYS" And _
UCase$(Left$(tdf.Name, 4)) <> "USYS" Then

with

If (tdf.Attributes And dbSystemObject) = 0 Then
 
Back
Top