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

    Unanswered: Export the description of the fields

    hi,

    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
    Sylvain

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    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.
    Code:
    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
       'MyDatabase.mdb
       'the created text file will be named
       'MyDatabase-structure.txt
    
       '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
       Next
       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
                Next
                If intPropertyCaption = -1 Then
                   strPropertyCaption = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Name
                Else
                   strPropertyCaption = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties(intPropertyCaption)
                End If
                If intPropertyDescription = -1 Then
                   strPropertyDescription = "<no description found>"
                Else
                   strPropertyDescription = dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Properties(intPropertyDescription)
                End If
                If dbMyDatabase.TableDefs(intTableDefs).Fields(intFields).Attributes = 17 Then
                   strPropertyType = "AutoNumber"
                Else
                   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"
                      Else
                         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"
                      Else
                         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
             Next
          End If
       Next
       Close #lngToFileHandle
       dbMyDatabase.Close
       Set dbMyDatabase = Nothing
    
    End Sub

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

    Sylvain

Posting Permissions

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