PRINT / EXPORT the Design View of a Table

Go to the Database window, click on Tools, Analyze, Documenter.  This will allow you to select which tables/forms/queries, etc. you want to see.  You can also specify how much info you want included under the options tab.

Use this as a starting point:

'BOC *************************************
Sub ShowDataInfo()
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim prp As DAO.Property
Dim fld As DAO.Field

    Set db = CurrentDb
    For Each tdef In db.TableDefs
        Debug.Print "Table: " & tdef.Name
        Debug.Print String(Len(tdef.Name) + 7, "=")
        For Each fld In tdef.Fields
            Debug.Print "Field: " & fld.Name
            For Each prp In fld.Properties
                If prp.Name <> Name Then
                    On Error Resume Next
                    Debug.Print Space(4) & prp.Name & ": " & prp.Value
                    If Err <> 0 Then
                        Debug.Print Space(4) & prp.Name & ": <Not printable>"
                    End If
                    On Error GoTo 0
                End If
            Next prp
            Debug.Print
        Next fld
    Next tdef
    db.Close
    Set db = Nothing
End Sub

Sub PrintDesign()
Dim db As DAO.Database
Dim tdef As DAO.TableDef
Dim fld As DAO.Field
Dim strDescription As String

    Set db = CurrentDb
    For Each tdef In db.TableDefs
        Debug.Print "Table: " & tdef.Name
        Debug.Print String(Len(tdef.Name) + 7, "=")
        For Each fld In tdef.Fields
            Debug.Print "*Field: " & fld.Name
            Debug.Print " -FieldType  : " & FieldTypeName(fld.Type)
            On Error Resume Next
            strDescription = fld.Properties("Description")
            If Error <> 0 Then
                strDescription = "<Not available>"
            End If
            On Error GoTo 0
            Debug.Print " -Description: " & strDescription
            Debug.Print
        Next fld
    Next tdef
    db.Close
    Set db = Nothing
End Sub

Function FieldTypeName(FieldType As DAO.DataTypeEnum) As String

    Select Case FieldType
    Case dbBigInt
        FieldTypeName = "dbBigInt"
    Case dbBinary
        FieldTypeName = "dbBinary"
    Case dbBoolean
        FieldTypeName = "dbBoolean"
    Case dbByte
        FieldTypeName = "dbByte"
    Case dbChar
        FieldTypeName = "dbChar"
    Case dbCurrency
        FieldTypeName = "dbCurrency"
    Case dbDate
        FieldTypeName = "dbDate"
    Case dbDecimal
        FieldTypeName = "dbDecimal"
    Case dbDouble
        FieldTypeName = "dbDouble"
    Case dbFloat
        FieldTypeName = "dbFloat"
    Case dbGUID
        FieldTypeName = "dbGUID"
    Case dbInteger
        FieldTypeName = "dbInteger"
    Case dbLong
        FieldTypeName = "dbLong"
    Case dbLongBinary
        FieldTypeName = "dbLongBinary"
    Case dbMemo
        FieldTypeName = "dbMemo"
    Case dbNumeric
        FieldTypeName = "dbNumeric"
    Case dbSingle
        FieldTypeName = "dbSingle"
    Case dbText
        FieldTypeName = "dbText"
    Case dbTime
        FieldTypeName = "dbTime"
    Case dbTimeStamp
        FieldTypeName = "dbTimeStamp"
    Case dbVarBinary
        FieldTypeName = "dbVarBinary"
    End Select
End Function
'EOC *************************************

HTH/EQTA
T.S.U. Mario Osorio
Punto Fijo, falcon, Venezuela

Ooops!

Forget about the ShowDataInfo() Sub ShowDataInfo, I used that for another example here ...

hotbudare:

What exactly does your code do, and what are the advantages over the Analyze...Documenter approach?

Thanks,

Tom

PsychoDazey:

Your answer has opened up some doors for me, thank you.

Once the Documentor generates the report, you can right-click on the report and Export the report under several file formats, including Excel.

Thanks!

You're welcome, glad I could help.  You can also automate this if you are going to be doing this frequently.

How can I automate this?

I anticipate I will be doing this probably once a month.

The reason I am doing this is some of the Field Names are not very descriptive of what they are.  I don't want to have to guess what the field is for.

Basically, I am trying to create a data dictionary for END USERS...very simple to understand, that describes the database.

Thanks

One thing the Documenter seems to lack is printing the Description from the Database Design View for a table.

This is the most important part of what I am doing?

Did I miss something in the Documenter options???

Thanks,

Tom

I have not used it myself, but you can enter this command in the on_click event for a command button:
docmd.RunCommand acCmdAnalyzeTable

Yes, you can print.  My Access is acting up right now so I cant run the documenter, but I have used it to print several times before.

Really?

Cool!

I actually used it for the same function as you.  I built a book for the database(s) I built in case someone else had to do some work they would have a handy reference.

PsychoDazey:

Cool!

Yeah, I just started working in MS Access / VBA a few weeks ago, and I am quickly realizing that I need something like a data dictionary to make somethings easier for me.

Thanks,

Tom

PsychoDazey:

I've taken some classes in school that talk about building a data dictionary if you are doing database work.

I am actually a database development newbie, especially in Access.

Before this job, I was working in a third party database called CodeBase, which is dBase IV / FoxPro compatible.  I accessed the db via the CodeBase API (in Delphi).  I did that for about 10 months before starting this job using Access.

Thanks again for your help.

Tom