print out Table Description Property (and Field Descriptions)

[Guest]

I am trying to create 2 report: A and B.

Report(A) contains the name and description for each of the tables in my
db, and then Report(B) that lists each each field name and description in
each table.

I am using Allison Balter's example in DAO and am getting an error that the
property ormethod is not available. I believe it is failing at the
description property. When I type in "tdf. " Intellisense does not show
description for tdf or any other variations I've tried. I don't want all the
properties the Documenter gives me, just a simple list so I can document and
share the definitions of the tables and fields. (Is the description property
hidden somewhere else?)

Thanks-
Loralee

******************
My code is:

Dim db As DAO.Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb
DoCmd.SetWarnings False
For Each tdf In db.TableDefs
DoCmd.RunSQL "INSERT INTO tblTableDefs" _
& "(tableName, TableDesc) " _
& "VALUES (" & tdf.Name & ", " & tdf.Description & ")"
Next tdf

DoCmd.SetWarnings True

 

[Douglas J. Steele]

The Description property doesn't exist unless you've assigned a description
to the table or field.

That means that your code has to handle the error that will occur when you
refer to a non-existant description.

Your Error Handler should be something like:

EH:
Select Err.Number
Case 3270
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume End_Routine
End Select

 

[Guest]

Thanks- I just got back to working on this, and I added the recommended
errorhandling. When I run from the immediate window I am still getting a
compile error "method or datamember not found" and when I step through it is
the Description property (in line: & "VALUES (" & tdf.Name & ", " &
tdf.Description & ")" ) that is erroring/light up.

Do the description properties of tables and fields "live" elsewhere? Did I
write something else wrong?
Thanks,

Loralee

 

[Douglas J. Steele]

Try

tdf.Properties("Description")

 

[Guest]

Perhaps I have the syntax wrong, but I'm getting a Property doesn't exist
error and NOTHING in my table. "Description" is what is highlighted when it
errs.

I incorporated your suggestion and now have:
**********************
Public Sub EnumerateTables() ' 1-22-07 doesn't work LPO

Dim db As DAO.Database
Dim tdf As TableDef
Dim fld As Field

Set db = CurrentDb
DoCmd.SetWarnings False
For Each tdf In db.TableDefs
DoCmd.RunSQL "INSERT INTO tblTableDefs" _
& "(tableName, TableDesc) " _
& "VALUES (" & tdf.Name & ", " & tdf.Properties("Description") &
")"
Next tdf

DoCmd.SetWarnings True

Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3270
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume
End Select

End Sub

 

[John Spencer]

You need to add another line to your code.
On Error GoTo ErrorHandler

I would also add another variable to your routine
Dim StrDescription as String
and try to assign the Description property to that variable for use in you
append query.

Also, I would add a test to eliminate system tables (they start with
"MSys"), but you may want them listed. Obviously, this is your choice.

If Instr(1,Tdf.Name,"MSYS") <> 1 then
' Do the work
End If

Public Sub EnumerateTables()
Dim db As DAO.Database
Dim tdf As TableDef
Dim fld As Field

On Error GoTo ErrorHandler

Set db = CurrentDb
DoCmd.SetWarnings False
For Each tdf In db.TableDefs
'Get the description and add quote marks around it
'and handle any description that has a quote mark in it
'by doubling the internal quotes.
strDescription = Chr(34) & _
Replace(tdf.Properties, Chr(34), Chr(34) & Chr(34)) & _
Chr(34)
'if that errors then the errhandler kicks in and assigns the string
"NULL"

DoCmd.RunSQL "INSERT INTO tblTableDefs" _
& "(tableName, TableDesc) " _
& "VALUES (" & tdf.Name & ", " & strDescription & ")"
Next tdf

DoCmd.SetWarnings True

Exit Sub
ErrorHandler:
Select Case Err.Number
Case 3270
strDescription = "Null"
Resume Next
Case Else
MsgBox Err.Number & ": " & Err.Description
Resume
End Select

End Sub


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..