Expert: Geoff - 2/16/2005

Question
Is there a way in Access 2003 to print what I visually can see on the screen in Table, Design Mode?  Meaning, I'd like to have a printout that includes the fieldnames, data types, and descriptions.  I know the documentor feature can print these items, but they come along with a bunch of other stuff I don't need (unless there is a way I don't know about to only print these 3 items I'd like).  And if there is not a way to do this directly in Access, can it be done via Excel somehow (via a macro, perhaps)?  Thanks very much for your time!

Answer
Hi Sheree,

I did something on this a few years ago, but couldn't find it, so I built a new module...

You will need to ensure that there is a refeence to DAO (Menu / tools / references - select MS DAO...)

This module goes through all tables in the DB and generates a report,  C:\TableDefinitions.txt

Here's the code - place this in a module, then run it...

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

Public Sub PrintTableDesign()

'...............................................................
' Procedure for printing out Access Database Table Definitions
'
'   By Geoff Johns GTS - gmjohns@fastmail.fm - Copyright 2005
'
'   You may use this software freely provided this header is
'   retained in your code. The Developer carries absolutely no
'   liabiliy for any consequences whatsoever.
'...............................................................

Dim tdf As DAO.TableDef, db As DAO.Database
Dim fld As DAO.Field, prp As DAO.Property
Dim DataType(30) As String, sDescrip As String

   Set db = CurrentDb
   Set prp = db.CreateProperty("Description", dbText)
   
   'Populate Datatype array
       DataType(1) = "Yes/No"
       DataType(2) = "Byte"
       DataType(3) = "Integer"
       DataType(4) = "Long"
       DataType(5) = "Currency"
       DataType(6) = "Single"
       DataType(7) = "Double"
       DataType(8) = "Date/Time"
       DataType(10) = "Text"
       DataType(11) = "OLE Obj"
       DataType(12) = "Memo"
       DataType(15) = "RepID"
       DataType(20) = "Decimal"
   
   'Open the reporting file
   Open "c:\TableDefinitions.txt" For Output As #1
   
   For Each tdf In db.TableDefs
       Write #1, "Table: " & tdf.Name
       'Debug.Print tdf.Name
       For Each fld In tdf.Fields
           sDescrip = ""
           For Each prp In fld.Properties
               If prp.Name = "Description" Then
                   sDescrip = prp.Value
               End If
           Next
           Write #1, Tab(5); fld.Name, Tab(30); DataType(fld.Type), Tab(45); sDescrip
           'Debug.Print , fld.Name, DataType(fld.Type), sDescrip
       Next
       'Debug.Print " "
       Write #1,
   Next

   'Close the reporting file
   Close #1

End Sub

XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

I hope this helps

Kind regards

Geoff