Duane's SQL Documenter Code

[Bud]

Way back on 2002-11-20 05:46:06 PST (was searching Google groups for a
similar answer), Duane posted the following reply to a question about
Documenter:

++++++++++++++++++++++++++++++++++++++++++++++
When I run the documenter on my PC (Windows ME), it creates a table in
an MDT file burried in my profile. The connection string of the
table is DATABASE=C:\WINDOWS\Application
Data\Microsoft\Access\ACWZUSR.MDT;TABLE=doc_tblObjects.

You can link to this table and create any type of report you want with
the table. For instance, this SQL will provide the table and field
names with field descriptions:

TRANSFORM First(doc_tblObjects_2.Extra1) AS FirstOfExtra1
SELECT doc_tblObjects.Name AS TableName, doc_tblObjects_1.Name AS FieldName,
doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize
FROM (doc_tblObjects AS doc_tblObjects_2 INNER JOIN doc_tblObjects AS
doc_tblObjects_1 ON doc_tblObjects_2.ParentID = doc_tblObjects_1.ID) INNER
JOIN doc_tblObjects ON doc_tblObjects_1.ParentID = doc_tblObjects.ID
WHERE (((doc_tblObjects_1.TypeID)=11))
GROUP BY doc_tblObjects.Name, doc_tblObjects_1.Name,
doc_tblObjects_1.Extra2, doc_tblObjects_1.Extra3
PIVOT doc_tblObjects_2.Name In ("Description:");

--
Duane Hookom
MS Access MVP

++++++++++++++++++++++++++++++++++++++++++++++

My question is related to the SQL code. When I run the code after
linking to the doc_tblobjects table, the code runs, but the fields are
blank. Am I missing a step?

Thanks!

 

[Duane Hookom]

Do you have descriptions for all of your fields?

 

[Bud]

Do you have descriptions for all of your fields?

Some, but not all.

 

[Duane Hookom]

Here is another query that might work for the basics:
SELECT doc_tblObjects.Name AS TableName, doc_tblObjects_1.Name AS FieldName,
doc_tblObjects_1.Extra2 AS FieldType, doc_tblObjects_1.Extra3 AS FieldSize
FROM doc_tblObjects AS doc_tblObjects_1 INNER JOIN doc_tblObjects ON
doc_tblObjects_1.ParentID = doc_tblObjects.ID
WHERE (((doc_tblObjects_1.TypeID)=11));