Solved

Generate list of field names and descriptions for tables in Access

Posted on 2007-11-15
3
278 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
Comment Utility


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
Comment Utility
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
Comment Utility
Thanks, appreciate the help!
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

763 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

7 Experts available now in Live!

Get 1:1 Help Now