Printing field names and descriptions

[MatMac]

Using Access 2000, the Documenter permits me to print the name, data type and size of table fields only, or alternatively ALL field properties. Printing all properties is more detail than I require and results in many hundred pages due to the 500 or so fields I have in some 30 tables.

What I would like to do is print the name, data type, size and DESCRIPTION only of table fields to include in my data dictionary.

Is there a way of doing this?

Many thanks.

 

[Pat Hartman]

You'll have to build the function yourself. I have a db where I do this by looping through the indexes and fields collections of the tabledefs collection and storing the properties in tables. I then make reports based on the extracted data. Help has sample code that should get you started. Look under collections. Make sure to include error trapping because the Description property is optional and if you reference an "empty" Description, your code will raise an error.

 

[MatMac]

Listing fields and descriptions

Thanks for that.

Problem is with no VBA experience I may stuggle to get some code together. Anyway, IU'll have a go. Thanks again.

 

[jempie]

did you manage to do this?

i'd be interested in hearing your thoughts as this would help save me a lot of time! .

 

[MatMac]

No, sorry, I didn't!

Mat.

 

[Steve R.]

Interesting. Handyman1 just wrote:

I hardly ever needed to post a question as I always found answers and sample code etc. for everything I needed.

This old post is just one such an example. Thanks to Pat Hartman and Alan57 for providing the clues necessary to figure out how print a field's description. Since no solution was provided back in 2003, I've included one now. Maybe MatMac might check-up on this.

Public Sub ModifiedGetTableObjects()
    Rem Print table field names to a TEXT file.
    Rem http://www.tek-tips.com/faqs.cfm?fid=2856
    Rem By faq702-2856
    On Error GoTo ERR_GetTableObjects
    Dim db As DAO.Database
    Dim tbl As TableDef
    Dim fld As Field
    Dim strDataBasePath As String
    Dim strDriveName As String
    strDataBasePath = CurrentProject.Path
    strDriveName = """" & Left(strDataBasePath, 1) & """"
    ChDrive strDriveName
    ChDir strDataBasePath
    Open "FieldNames.txt" For Output As #1    ' Open file for output.
    Print #1, "Fields in the ConsistencyMain Table"
    Print #1, "___________________________________"
    Print #1, " "
    Print #1, Format(Now, "short date")
    Print #1, ""; ""
    Set db = CurrentDb
    For Each tbl In db.TableDefs
        If tbl.Name = csConsistencyMain Then
            Debug.Print "Table: " & tbl.Name & " *******"
            For Each fld In tbl.Fields
                Debug.Print fld.Name, fld.Properties("Description")
                Print #1, fld.Name; Tab(25); fld.Properties("Description")
        Next fld
        End If
    Next tbl
    Close #1    ' Close file.
    Debug.Print "Drive Name: "; strDriveName
    Debug.Print "Results Printed here: "; strDataBasePath
    Exit Sub
ERR_GetTableObjects:
    Debug.Print Err.Description, Err.Number
    Resume Next
End Sub