Code to list Tables & Fields in a mdb

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jim Pockmire - 17 Nov 2006 19:45 GMT

What code can I use to list all of the tables in a database, or all of the
fields in a table?

Reply to this Message

Klatuu - 17 Nov 2006 20:10 GMT

Use the built in Documenter. It will provide a printout of whatever you
select.
Tools, Analyze, Documenter

> What code can I use to list all of the tables in a database, or all of the
> fields in a table?

Reply to this Message

fdcusa - 18 Nov 2006 07:50 GMT

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

Reply to this Message

Douglas J. Steele - 18 Nov 2006 13:55 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Option Compare Database
> Option Explicit
[quoted text clipped - 28 lines]
>
> End Sub

Reply to this Message