Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Generate list of field names and descriptions for tables in Access

Posted on 2007-11-15
3
Medium Priority
?
329 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 2000 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

598 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