Solved

Generate list of field names and descriptions for tables in Access

Posted on 2007-11-15
3
301 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
  • 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access 2016 7 35
sql statement - 9 25
How to use a single FE file for users with different access versions? 8 20
Modify report 8 7
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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 …

777 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