Solved

Generate list of field names and descriptions for tables in Access

Posted on 2007-11-15
3
290 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 119

Accepted Solution

by:
Rey Obrero 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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 …

895 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now