Solved

Generate list of field names and descriptions for tables in Access

Posted on 2007-11-15
3
319 Views
Last Modified: 2013-11-27
I have the following code I am running to build a data dictionary.  I want the dictionary to contain field names and descriptions for each table.  I can get the names fine, I just can't figure out how to get the descriptions.  Here is my code:
Sub ExamineTable(TableName As String)
Dim currentDB As Database
Dim mytable As TableDef
Dim fieldName As String
Dim fieldDescription As String
Dim i As Integer

Set currentDB = Application.currentDB
Set mytable = currentDB.TableDefs(TableName)

   
For i = 0 To mytable.Fields.Count - 1
    fieldName = mytable.Fields(i).Name
'THIS IS WHERE IT BLOWS UP (BELOW).  I DONT KNOW HOW TO ACCESS THE DESCRIPTION OR CAPTION
    fieldDescription = mytable.Fields(i).Description
   
    DoCmd.RunSQL "Insert tblDictionary (FieldName, FieldDescription) " _
        & "Values ('" & fieldName & "','" & fieldDescription & "');"

    Next i

End Sub
0
Comment
Question by:PsychoDazey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 20292275


try this

Sub GetFieldDescr(strTable As String) ' As String
On Error Resume Next
Dim tbl As DAO.TableDef
    Dim fld As DAO.Field
    For Each tbl In CurrentDb.TableDefs
        For Each fld In tbl.Fields
            If fld.Properties("Description") <> vbNullString Then
            '    Debug.Print fld.Name & vbTab & fld.Properties("Description")
            CurrentDb.Execute "insert into tblDictionary (FieldName, FieldDescription) " _
        & " Values ('" & fld.Name & "','" & fld.Properties("Description") & "');"
           End If
        Next fld
    Next tbl

End Sub


0
 
LVL 6

Author Comment

by:PsychoDazey
ID: 20292315
Perfect, thanks!  I also added this to eliminate apostrophes for those following along:
Replace(fld.Properties("Description"), "'", "")
0
 
LVL 6

Author Closing Comment

by:PsychoDazey
ID: 31409419
Thanks, appreciate the help!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question