Where are the table.field descriptions stored? 10

[Stoffel24]

Hi

When looking at the design view of a table in Access, there is a column called "Description". I was wondering if anyone knows where this data is stored if you wish to extract it with say a SQL statement in VBA.

Thanks for the help.

 

[TonyJollans]

Hi Stoffel24,

Not SQL but you can do it in VBA. You need to use the Description Property of the Field and you will have to create it if there isn't any description text to start with. See thread705-545751 for some code.

Enjoy,
Tony

 

[jrbarnett]

I don't know how to get it through an SQL statement, but it is available as a property called "Description" of the table itself (available on right click in table) and the individual field descriptions are available using the description property of the field. Thus:

Dim db As Database
Dim tdf As Tabledef
Dim fld As Field

Set db = CurrentDb
For Each tdf In db.TableDefs
  Debug.Print tdf.Name & " " & tdf.Properties("Description")
  Debug.Print "---"
  For Each fld In tdf.Fields
    Debug.Print "-->" & tdf.Name & " " & tdf.Properties("Description")
  Next fld
  debug.Print "-- End of table " & tdf.Name & "--"
Next tdf

Set db = Nothing

You need to set a reference to the Microsoft DAO 3.6 Object library if using this in Access 2000 or 2002 (XP) but it should work fine in 97 with no modification.

 

[Stoffel24]

Thanks Tony and jr.

I had a look at both the postings and have still not figured it out. Tony your posting seems good but I am battling to see how I can get the description for a field back. (I ran the code and it works fine but my knowledge of Access VBA isn't great - I'm more an Excel kinda guy - so I couldn't get it to return the description property.)

jr your code gives me an error whenever you try run the segment ".Properties("Description&quot"

Can either of you give me a few more pointers?

Thanks

 

[jrbarnett]

The function below will retrieve the description property of a database object; is pinched from my Access 97 Documenter addin source code.

Public Function GetDescription(obj As Object) As String
' Parameters: obj - any object
' Return Value: String - the value of the description property.
' Author:   John Barnett
' Date:     10 July 2001.
' Description: Returns the description property of the object passed as a parameter.

Dim prp As Property

GetDescription = ""
For Each prp In obj.Properties
    If prp.Name = "Description" Then
        GetDescription = prp.Value
        Exit For
    End If
Next
End Function

I forgot that the property won't exist until some data is actually in the place, so if you put on error resume next at the top of the code it works as well, but remember that if it is null, the whole string evaluates to null so prints nothing if no description is set.

John

 

[TonyJollans]

Hi Stoffel24,

John's code is good but there is no need to go through the whole collection to find a single property, you can ask for it directly:

Function GetDescription(Obj As Object) As String

On Error Resume Next
GetDescription = Obj.Properties!Description

End Function

Incidentally, there is no need to worry about Nulls - all results are strings, even if empty.

Enjoy,
Tony

 

[Stoffel24]

John and Tony thanks a lot. I have managed to get that all working now and I am most grateful. Each of you get a

 

[Quehay]

This is something I've been trying to do for a while (extract description programmatically).

If I do this:

[tt] For Each fld In tdf.Fields

For Each prp In fld.Properties
Debug.Print prp.NAME
Next[/tt]

I get this list:

[tt]Value
Attributes
CollatingOrder
Type


Name
OrdinalPosition
Size
SourceField
SourceTable
ValidateOnSet
DataUpdatable
ForeignName
DefaultValue
ValidationRule
ValidationText
Required
AllowZeroLength
FieldSize
OriginalValue
VisibleValue
ColumnWidth
ColumnOrder
ColumnHidden
Format
GUID
[/tt]

I read the thread Tony pointed to, and it would seem that you could create the property and then add a description value to it, but this doesn't work for getting the descriptions added interactively through the GUI. Am I missing something??

 

[TonyJollans]

Hi Quehay,

If there is a description added through the GUI there WILL be a Property called Description. If there isn't any description visible in the GUI there won't be a Property called Description.

Conversely, if you add a Property called Description via VBA code, the value you assign to that Property will appear in the GUI next time you look.

Hope that makes it clearer.

Enjoy,
Tony

 

[Quehay]

Tony,

Thanks! I'd suspected that but was getting the prop list only for the first table grabbed (and I'd created a test description in another table).

This confirmed your assertion after adding a dummy description in one of the tables.

Cheers!

[tt]Sub test()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property

For Each tdf In CurrentDb.TableDefs

For Each fld In tdf.Fields

For Each prp In fld.Properties

If prp.NAME = "description" Then

Debug.Print tdf.NAME & " " & fld.NAME & " " &
prp.Value

End If

Next

Next
Next[/tt]

 

[zevw]

Tony

Thanks, your post hit it on the button, 7 Months Later.

I really needed the description.

 

[TonyJollans]

Hi zevw,

Thanks for posting that. It's good to know that Tek-Tips works as a repository that people can get information out of months after it's put there.



Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[LarryDeLaruelle]

Quehay:

Thanks for the code and have a star. I've been looking for a way to get the description out of table design without printing the documentor and this did it.

I made a small tweak to write the results to a table:

Public Sub GetDesc()
Dim dbTemp As Database
Dim rsTemp As Recordset
Dim tdf As TableDef
Dim fld As Field
Dim prp As Property

Set dbTemp = CurrentDb
Set rsTemp = dbTemp.OpenRecordset("tblFieldDesc&quot

For Each tdf In CurrentDb.TableDefs
For Each fld In tdf.Fields
For Each prp In fld.Properties
If prp.Name = "Description" Then
With rsTemp
.AddNew
!tablename = tdf.Name
!FieldName = fld.Name
!Description = prp.Value
.Update
End With
End If
Next
Next
Next
rsTemp.Close
Set dbTemp = Nothing

End Sub


Does anyone know why JR's code chokes on the
tdf.Properties("Description&quot
line?

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[Quehay]

Hey Larry thanks yourself and Hi--I haven't "seen" you in a while. I recently posted this and don't remember the thread--not trying to blow my own horn here, but I LOVE this routine because it makes a great data dictionary with column descriptions and table descriptions (**and error ignored if they're not there--Tony pointed out above that the Description property is not a permanent member of the properties collection--it only exists if a description is created for table or field):

Cheers!

[tt]
Public Sub DocumentTables()
'Requires function FieldType

On Error GoTo Error_DocumentTables

Dim DB As DAO.Database
Dim Rs As DAO.Recordset
Dim TDF As DAO.TableDef
Dim FLD As DAO.Field
Dim strSQL_DROP As String
Dim strSQL_CREATE As String

'Delete existing copy of this table
strSQL_DROP = "DROP TABLE data_dictionary;"

DoCmd.RunSQL strSQL_DROP

strSQL_CREATE = "CREATE TABLE data_dictionary" & _
"(table_name varchar(50),table_description varchar(100), field_name varchar(50),field_description varchar(100)," & _
"ordinal_position NUMBER, data_type varchar(15)," & _
"length varchar(5), default varchar(30), primary_key varchar(10)," & _
"nullable varchar(20));"

DoCmd.RunSQL strSQL_CREATE

Set DB = CurrentDb()

Set Rs = DB.OpenRecordset("data_dictionary&quot

With Rs
For Each TDF In DB.TableDefs
If Left(TDF.NAME, 4) <> &quot;Msys&quot; Then

For Each FLD In TDF.Fields
.AddNew
!table_name = TDF.NAME
!table_description = TDF.Properties(&quot;description&quot
!field_name = FLD.NAME
!field_description = FLD.Properties(&quot;description&quot
!ordinal_position = FLD.OrdinalPosition
!data_type = FieldType(FLD.TYPE)
!length = FLD.Size
!Default = FLD.DefaultValue
!nullable = &quot;YES&quot;

If FLD.REQUIRED Then
!nullable = &quot;NO&quot;
End If

.Update
Next
End If
Next

End With

MsgBox &quot;Tables have been documented&quot;, vbInformation, &quot;TABLES DOCUMENTED&quot;

Rs.Close
DB.Close

Exit_Error_DocumentTables:

Set TDF = Nothing
Set Rs = Nothing
Set DB = Nothing

Exit Sub

Error_DocumentTables:

Select Case Err.Number

Case 3376

Resume Next 'Ignore error if table not found
Case 3270 'Property Not Found

Resume Next
Case Else

MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_DocumentTables

End Select

End Sub
Private Function FieldType(v_fldtype As Integer) As String
On Error GoTo Error_FieldType

Select Case v_fldtype
Case dbBoolean
FieldType = &quot;Boolean&quot;
Case dbByte
FieldType = &quot;Byte&quot;
Case dbInteger
FieldType = &quot;Integer&quot;
Case dbLong
FieldType = &quot;Long&quot;
Case dbCurrency
FieldType = &quot;Currency&quot;
Case dbSingle
FieldType = &quot;Single&quot;
Case dbDouble
FieldType = &quot;Double&quot;
Case dbDate
FieldType = &quot;Date&quot;
Case dbText
FieldType = &quot;Text&quot;
Case dbLongBinary
FieldType = &quot;LongBinary&quot;
Case dbMemo
FieldType = &quot;Memo&quot;
Case dbGUID
FieldType = &quot;GUID&quot;
End Select

Exit_Error_Fieldtype:
Exit Function

Error_FieldType:
MsgBox Err.Number & &quot;: &quot; & Err.Description
Resume Exit_Error_Fieldtype

End Function
[/tt]

Jeffrey R. Roberts
Insight Data Consulting
Access, SQL Server, & Oracle Development

http://www.InsightDataConsulting.com

 

[LarryDeLaruelle]

Jeffery:

That is awesome and worth several stars.

Haven't been around for a while due to holidays and work load; glad to be back.

I used to use an Excel spread sheet to document my tables and fields. This routine will save me a lot of work and make documenting less of a chore (still a pain but a necessary pain).

Thanks again.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[VelNPS]

I enjoyed these postings but am trying to find get at the table properties and eventualy create a datadictionary in ADO since I do not trust DAO to stay around all that long.

Does anybody understand the starting objects to begin to get at these in ADO? Do I need to use ADOX?

Thanks Vel

 

[Quehay]

Hey don't let the "death of DAO" paranoia get you--when it goes Access probably will too. DAO is so much better for native Access stuff--even Microsoft still says that it's faster for native Jet work.

ADOX is the one you want though...set a reference to it and look in the object browser. It should be pretty simple to do the above. Look at table & column objects.


Jeff Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[RoyVidar]

Theres a thread in the Visual Basic (Microsoft) Database forum thread709-752030, where MarkSweetland demonstrates some techniques (might also experiment with currentproject.connection in stead of the connection used there too?)

Roy-Vidar