Printing or Exporting Field Descriptions from a Table

[Jennifer]

I am working with a DB that I did not create... The original users/creators
have gone on to other places unknown and we are now trying to decifer their
data. The data comes from questionnaires that were entered into the tables.
Each question from the questionnaire was given a code and the only place that
tells what the code is can be found in the description from the design view
of the table. How do I get those descriptions into Word/Excel or any other
format that can be printed or copied? Thanks.

 

[Ken Sheridan]

The built in documenter will include the field description if you opt to
include field properties. Or you can put them in a table, in which you can
then base a report, with a little bit of VBA, however. First create a table
TableDefinitions by running pasting the following into a new query in SQL
view and then running it (you don't need to save the query)

CREATE TABLE TableDefinitions (
TableName TEXT(50) NOT NULL,
FieldName TEXT(50) NOT NULL,
FieldType INTEGER NOT NULL,
FieldDesc TEXT(255),
CONSTRAINT PrimaryKey PRIMARY KEY (TableName, FieldName));

Then paste the following procedure into a standard module:

Public Sub DocumentTable(strTable As String)

Dim dbs As DAO.Database, tdf As DAO.TableDef, fld As DAO.Field
Dim strSQL As String

Set dbs = CurrentDb
Set tdf = dbs.TableDefs(strTable)

' loop through table's Fields collection and
' insert row into table for each field
For Each fld In tdf.Fields
strSQL = "INSERT INTO TableDefinitions "
strSQL = strSQL & "(TableName,FieldName,"
strSQL = strSQL & "FieldType,FieldDesc) "
strSQL = strSQL & "VALUES(" & Chr(34)
strSQL = strSQL & tdf.Name & Chr(34) & "," & Chr(34)
strSQL = strSQL & fld.Name & Chr(34) & ","
strSQL = strSQL & fld.Type & ","
' if no Description property exists this will
' raise an error, so handle error and insert
' NULL into FieldDesc colum
On Error Resume Next
strSQL = strSQL & Chr(34) & fld.Properties("Description") & Chr(34)
If Err <> 0 Then strSQL = strSQL & "NULL"
On Error GoTo 0
strSQL = strSQL & ")"
dbs.Execute strSQL
Next fld

End Sub

You can then call it with:

DocumentTable "NameOfYourTableGoesHere"

to insert rows into the TableDefinitions table for each field in the table
concerned. The field type column will contain the numeric values of the
various DAO constants for each field type, e.g. for a text field the value
will be 10 which is the value of the dbText constant. If you look up this
constant in the object browser you'll find the other data type constants
listed with it, so it would be quite easy to create a DataTypes table with
the numeric values in one column and a text value in another which you could
join to the TableDefinitions table in a query to return the text of the data
types if you wished.

Incidentally the table sounds badly designed. Having a column for each
question is not the right way to go. A better design would have a row for
each question in a related table.

Ken Sheridan
Stafford, England

 

[Ken Sheridan]

It does in fact, so long as you opt to include field properties in the
output. You get all the properties of course, not just the description.

Ken Sheridan
Stafford, England

Sorry, that doesn't work. I've been looking at this screen for too long.

Try the menu item Tools -> Analyze -> Documentor.

I am working with a DB that I did not create... The original users/creators
have gone on to other places unknown and we are now trying to decifer their

[quoted text clipped - 3 lines]

of the table. How do I get those descriptions into Word/Excel or any other
format that can be printed or copied? Thanks.