Getting the description information from a table column (1 Viewer)

[pacctono]

I am tryng to print the table information:

Table name

And for each column ("x" represents the index of the column):
Column name: CurrentDb.TableDefs(tableName).Fields(x).Name
column type: CurrentDb.TableDefs(tableName).Fields(x).Type
column length: CurrentDb.TableDefs(tableName).Fields(x).Size
and
column description: CurrentDb.TableDefs(tableName).Fields(x).Properties("Description")

The problem is that I am getting an error when a column has not description.
Is there an implicit function to check if the "Description" property exists?

If not, I could create that function with a loop inside checking if any Property is "Description".
Now, if it possible to pass "CurrentDb.TableDefs(tableName).Fields(x)" as the first argument? What type would it be?

 

[plog]

I have code for this, it works for both tables and queries, so you might just take the part you need or just always pass it "Table" for the type:

Public Function get_ObjectDescription(ByVal in_Object As String, ByVal in_Type As String) As String
  ' gets description of in_Object
 
    Dim ret As String                   ' return value, will hold description

    ret = ""
    ' default return value is nothing
    
    On Error Resume Next
    For i = 0 To 0
    ' gets description if it exists for object based on its type
        If in_Type = "Query" Then ret = CurrentDb.QueryDefs(in_Object).Properties("Description")
        If in_Type = "Table" Then ret = CurrentDb.TableDefs(in_Object).Properties("Description")
    Next

    get_ObjectDescription = ret

End Function

 

[theDBguy]

Is there an implicit function to check if the "Description" property exists?

I don't think there is. However, you can create one or use something like this example.

Microsoft Access tips: DAO Programming Code Examples

VBA code using the DAO library in Microsoft Access.

allenbrowne.com

 

[Pat Hartman]

Unless you specifically create certain properties, they are undefined. You will need to trap the error and ignore it so the code can continue.

 

[MajP]

[ code]
Public Sub test()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblstaging")
Dim fld As DAO.Field
For Each fld In rs.Fields
If HasProperty(fld, "description") Then
Debug.Print fld.Name & " " & fld.Properties("description").Value
Else
Debug.Print fld.Name & " No description"
End If
Next fld
End Sub

Public Function HasProperty(Obj As Object, propName As String) As Variant
On Error GoTo errLbl
Dim prpValue As String
prpValue = Obj.Properties(propName).Value
HasProperty = True
Exit Function
errLbl:
If Not Err.Number = 3270 Then
Debug.Print Err.Number & Err.Description
End If
End Function
[/code]

 

[strive4peace]

hi @pacctono

if you're trying to document the structure of your database, perhaps you might like the Analyzer ... it's free, with source code. Not only does it document structure, but it also does a value analysis and a lot more. It also documents descriptions! In fact, getting a data dictionary with descriptions is what caused me to write this tool originally, back in the early 90's ... and it has gotten better since then!

download:
https://msaccessgurus.com/tool/Analyzer.htm

here's a video on YouTube about it (keep meaning to upload another thumbnail -- not sure what happened to the file that used to be there)

If you have any problems, make sure you aren't running it in the Zip file, and you could possibly have to Compile it.

 

[pacctono]

I have code for this, it works for both tables and queries, so you might just take the part you need or just always pass it "Table" for the type:

Public Function get_ObjectDescription(ByVal in_Object As String, ByVal in_Type As String) As String
  ' gets description of in_Object

    Dim ret As String                   ' return value, will hold description

    ret = ""
    ' default return value is nothing
   
    On Error Resume Next
    For i = 0 To 0
    ' gets description if it exists for object based on its type
        If in_Type = "Query" Then ret = CurrentDb.QueryDefs(in_Object).Properties("Description")
        If in_Type = "Table" Then ret = CurrentDb.TableDefs(in_Object).Properties("Description")
    Next

    get_ObjectDescription = ret

End Function

Thanks, your code help me to soled the problem:

Function descripcionColumna(campo As Field) As String
On Error GoTo noExisteDescripcion

descripcionColumna = campo.Properties("Description")

salir_descripcionColumna:
Exit Function

noExisteDescripcion:
descripcionColumna = ""
Resume salir_descripcionColumna
End Function