Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003

    Unanswered: Export the description of the fields


    I have to write the documentation of a database ACcess. The name of the fields are very ugly ... but there is a description of each field.

    I would like to use these descriptions to make easier (and faster) the writing of my documentation.

    Is it possible to export (to Word, Visio, Excel or something else) the description of the fields of the tables of a database Access.

    If someone has any idea that could help ...

    Thank you in advance

  2. #2
    Join Date
    Jan 2003
    Rockville, MD
    Hi Sylvain,

    This might be close to what you're looking for. You'll need the DAO reference, and you might need the VBA extensibility reference.
    Sub ListStructure()
       'create a text file listing the structure
       'of all tables in the specified database
       'text file will be named the same name as
       'the database, minus the extension, with
       '-structure.txt appended
       'for example, if your database is named
       'the created text file will be named
       'output is tab-delimited
       Dim dbMyDatabase As Database
       Dim intPosition As Integer
       Dim intPropertyCaption As Integer
       Dim intPropertyDescription As Integer
       Dim intTableDefs As Integer
       Dim intFields As Integer
       Dim intProperty As Integer
       Dim lngToFileHandle As Long
       Dim strAppPath As String
       Dim strToFileName As String
       Dim strPropertyCaption As String
       Dim strPropertyDescription As String
       Dim strPropertyType As String
       'alternate database
       'Set dbMyDatabase = OpenDatabase("C:\MyFolder\MyDatabase.mdb")
       Set dbMyDatabase = CurrentDb
       strAppPath = dbMyDatabase.Name
       For intPosition = Len(strAppPath) To 1 Step -1
          Select Case Mid$(strAppPath, intPosition, 1)
          Case "\", "/"
             strAppPath = Left$(strAppPath, intPosition)
             Exit For
          End Select
       strToFileName = Mid$(dbMyDatabase.Name, Len(strAppPath) + 1, Len(dbMyDatabase.Name) - Len(strAppPath) - 4) & "-Structure.txt"
       If Dir$(strAppPath & strToFileName) = strToFileName Then
          Kill strAppPath & strToFileName
       End If
       'open the text file
       lngToFileHandle = FreeFile
       Open strAppPath & strToFileName For Output As #lngToFileHandle
       'begin documenting tables/fields
       Print #1, Chr(9) & "Type" & Chr(9) & "Caption" & Chr(9) & "Description"
       For intTableDefs = 0 To dbMyDatabase.TableDefs.Count - 1
          If Left$(UCase$(dbMyDatabase.TableDefs(intTableDefs).Name), 4) <> "MSYS" Then
             Print #1,
             Print #1, "Table: " & dbMyDatabase.TableDefs(intTableDefs).Name
             For intFields = 0 To dbMyDatabase.TableDefs(intTableDefs).Fields.Count - 1
                intPropertyCaption = -1
                intPropertyDescription = -1
                For intProperty = 0 To dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties.Count - 1
                   Select Case UCase$(dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties(intProperty).Name)
                   Case "CAPTION"
                      intPropertyCaption = intProperty
                   Case "DESCRIPTION"
                      intPropertyDescription = intProperty
                   End Select
                If intPropertyCaption = -1 Then
                   strPropertyCaption = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Name
                   strPropertyCaption = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties(intPropertyCaption)
                End If
                If intPropertyDescription = -1 Then
                   strPropertyDescription = "<no description found>"
                   strPropertyDescription = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties(intPropertyDescription)
                End If
                If dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Attributes = 17 Then
                   strPropertyType = "AutoNumber"
                   Select Case dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Type
                   Case 1
                      On Error Resume Next
                      If dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties("Format") = "" Then
                         strPropertyType = "Binary"
                         strPropertyType = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties("Format")
                      End If
                      On Error GoTo 0
                   Case 3
                      strPropertyType = "Integer"
                   Case 4
                      strPropertyType = "Long Integer"
                   Case 7
                      On Error Resume Next
                      If dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties("Format") <> "Percent" Then
                         strPropertyType = "Double"
                         strPropertyType = "Double(Percent)"
                      End If
                      On Error GoTo 0
                   Case 8
                      strPropertyType = "Date"
                   Case Else
                      strPropertyType = "Text(" & dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Size & ")"
                   End Select
                End If
                Print #1, Chr(9) & strPropertyType & Chr(9) & strPropertyCaption & Chr(9) & strPropertyDescription
          End If
       Close #lngToFileHandle
       Set dbMyDatabase = Nothing
    End Sub

  3. #3
    Join Date
    Sep 2003
    Thank you very much, it is exactly what I was looking for!


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts