Querying Column Description from system tables 3

[tkatlas]

I am trying to create a listing that has the table name, column name and column description for all user tables in my database.&nbsp;&nbsp;I only that came close is creating a huge report from Tools/Analyze/Documentor which provides a report with way too much data and pages.&nbsp;&nbsp;Does anyone know where Access Stores this information for retrieval?<br>Thanks...

 

[Cornerstone]

First create a table in your database to store all of the information you want. Then try this code to suit your own situation.<br><br>Sub TableDocumenter()<br>Dim db As DAO.Database<br>Dim tbl As DAO.TableDef<br>Dim fld As DAO.Field<br>Dim rst As DAO.Recordset<br><br>Set db = CurrentDb<br>Set rst = db.OpenRecordset(&quot;tblTableDocumentation&quot<br><br>For Each tbl In db.TableDefs<br>&nbsp;&nbsp;&nbsp;&nbsp;If Not Left(tbl.Name, 4) = &quot;msys&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;For Each fld In tbl.Fields<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.AddNew<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!TableName = tbl.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!FieldName = fld.Name<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst!Size = fld.Size<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;'keep adding what you want<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;rst.Update<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Next<br>&nbsp;&nbsp;&nbsp;&nbsp;End If<br>Next<br>rst.Close<br>db.Close<br>End Sub<br>

 

[tkatlas]

This didn't really answer my question.&nbsp;&nbsp;The descriptions are already in the access database and I would like to query them or create a report to show them... Do you know the code to crab this information from the system.&nbsp;&nbsp;I know it is stored out there somewhere.&nbsp;&nbsp;Thanks...

 

[Halifax]

(depending on version of Access)<br><br>In Access97: go into (T)ools;Options and click ShowSystemObjects = TRUE. Apply; OK<br><br>Go to Database window, click Tables<br>Go into MySysObjects<br>The TYPE field will indicate Table, form, report etc.<br>The name field should be self evident.<br><br>Build your query on this table, and use for report, or combobox etc.<br><br>Then hide the tables again.<br><br>

 

[tkatlas]

I have Access 2000 and when I view the MySysObjects table, I can tell by the type field that it is a table and the name field shows the column name... BUT, I still don't see the description column (info) anywhere in here.&nbsp;&nbsp;Maybe it is different in 2000.&nbsp;&nbsp;Sincerely,,,, Still Looking THANKS

 

[Rosydeal]

I'm confronting this problem as well. I want to be able to include the 'Description' (not Name) property of the columns in the tables of my database. When looking at a table design in MS Access, the description is one of the columns you can maintain. My object model chart doesn't show any property that looks like 'Description'. The Access documentor picks this up so I assume there's a way to get at it in code. This would be very helpful for keeping updated documentation on the database.

 

[raskew]

The &quot;Description&quot; property is found in the table defs,
e.g. tdf.properties(&quot;Description&quot

I've previously provided code for this in another forum
so, rather than repeat the rather lengthy code, suggest
you look here:

http://bbs.elementkjournals.com/bbs...se&Searchpage=0&Limit=25&Old=1year&Main=28763

 

[JimHorton]

But that is table description..here is field:
Sub ShowDesc()
Dim db As Database, td As TableDef, i As Integer, j As Integer
Set db = CurrentDb
Set td = db.TableDefs(&quot;tblGenericFieldName&quot '
On Error Resume Next
For j = 0 To db.TableDefs.Count - 1
Set td = db.TableDefs(j)
Debug.Print &quot;Table: &quot; & td.Name
For i = 0 To td.Fields.Count - 1
Debug.Print &quot; &quot; & td.Fields(i).Name & &quot; &quot; & td.Fields(i).Properties(&quot;Description&quot
Next i
Next j
End Sub
Now load that into a table and you're done
--Jim

 

[RCPD700]

If you want to see the system objects, on your Access menu bar click on Tools, Options then the View tab. Make sure System Objects is checked. Click Apply. You will then be able to access all the &quot;MSys&quot; prefixed tables and find the info you want.