Exporting metadata (table field descriptions) with table

[giffordpinchot]

Hello everyone,

I frequnetly need to export tables for others to use. I export them as .csv files usually and I'd like a way to include what I call metadata in the .csv file.

ideally, it would appear on the top of the file before the comma-delimited data actually starts.

I can't figure out how to include the table field descriptions in a file.

any help?

thanks,
giffordpinchot

 

[gromit]

Hi -

Haven't done this sort of thing before, but I think that you would need to write the VBA to do the whole export. You can have the code examine the table and write the fields out, including descriptions.

The .csv format would be speciific only to what you are doing, so you would need to provide some means of reconstituting the files on the other end.

Is this addressing your question?

- g

 

[giffordpinchot]

Thanks Gromit.

You've got it but it's the code to export the field descriptions that I don't understand.

I can export a table with all the fields but how do I include the description data with each field?

thanks again,

gifford

 

[gromit]

I think it is a property of the field, e.g.

Dim fld as Field

debug.print fld.property.description

See if that works, otherwise I'll have to dig a little....

 

[gromit]

Nope, that's not quite right... I'll have to see if I can find that again. [Had it figured out somewhere once]

 

[giffordpinchot]

thanks, I'm anxiously awaiting!

 

[gromit]

Hi -

See this for a sample -

Dim dbs As DAO.Database
Dim tbl As DAO.TableDef

Dim fld As DAO.Field

Set dbs = DBEngine(0)(0)
Set tbl = dbs.TableDefs("tblOfficers")
Set fld = tbl("Name")

Debug.Print fld.Name
Debug.Print "*" & fld.Properties("Description") & "*"

dbs.Close
Set dbs = Nothing
Set tbl = Nothing
Set fld = Nothing

There is something odd about the Description property and MSDN's info suggests that it is not a standard part of the SQL tabledefs. The code sample about should work, but there are a few things to beware of.
a) It crashes if there is no Description set.
b) If you enter a description and then change it. It may not be reflected in the description until you compact the database.

hth,

-g

 

[giffordpinchot]

I have this which does what I want but it only writes the info to the Immediate Window.

I can't get it to write to the table "fielddesc"

Function retrieve_fld_descriptions()
    On Error GoTo E_Handle
    Dim tdf As TableDef
    Dim fld As Field
    Dim desc As Variant
    Dim db As Database
    Set db = CurrentDb
    Dim Tablename As Variant
    Dim Fieldname As Variant
    
    
      
    DoCmd.SetWarnings WarningsOff
    DoCmd.OpenQuery "qlastupdate", acViewNormal, acReadOnly       'runs make-table query to repopulate "lastupdate" table
    DoCmd.SetWarnings WarningsOn
        
    DoCmd.OpenTable "fielddesc", acViewNormal, acEdit   'opens new table to be populated
    

    For Each tdf In db.TableDefs
         If Left(tdf.Name, 4) <> "MSys" Then
             For Each fld In tdf.Fields
                Tablename = tdf.Name
                Fieldname = fld.Name
                desc = fld.Properties("Description")
                
            Next fld
        End If
    Next tdf
sExit:
    Set db = Nothing
    Exit Function
E_Handle:
    Select Case Err.Number
        Case 3270
            Debug.Print vbTab & fld.Name & vbTab & "n/a"
            Resume Next
        Case Else
            MsgBox Err.Description, vbOKOnly + vbCritical, "Error: " & Err.Number
            Resume sExit
    End Select
End Function

what am I missing?

we're getting there. Note that I'm a beginner so my code is likely butchered pieces of code I've picked up elsewhere plus my attempts at insertions

 

[gromit]

Sorry for the delays - dodging the metaphorical alligators...

1. Are you trying to duplicate the table or write code that will create the csv file? (I think I'm getting a little lost in the posts).

2. Probably some of the trouble with the description is that it is "non-standard" property of a field. I.e. the description property does not exist until it is added. So you can't modify the description property if you have not created it with the ADD method.

See
http://msdn.microsoft.com/library/d...ry/en-us/vbaac11/html/acmthAdd_HV03084638.asp

3. One way to check to see if the Description property exists is to loop through all the properties and see if one is called description (sort of sounds goofy, but that's Microsoft for you...

Private Sub Command0_Click()
    Dim dbs As DAO.Database
    Dim tbl As DAO.TableDef
    
    Dim fld As DAO.Field
    Dim prp As DAO.Property
    
    Set dbs = DBEngine(0)(0)
    Set tbl = dbs.TableDefs("tblName")
    
    For Each fld In tbl.Fields
        Debug.Print fld.Name
        For Each prp In fld.Properties
            If prp.Name = "Description" Then
                Debug.Print "*" & fld.Properties("Description") & "*"
            End If
        Next prp
    Next fld
    
    dbs.Close
    Set dbs = Nothing
    Set tbl = Nothing
    Set fld = Nothing
    
End Sub

4. I copied this example straight out of the MS Access documentation, it might also shed a little light on things.

Sub CreateTableDefX()

    Dim dbsNorthwind As Database
    Dim tdfNew As TableDef
    Dim prpLoop As Property

    Set dbsNorthwind = OpenDatabase("Northwind.mdb")

    ' Create a new TableDef object.
    Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

    With tdfNew
        ' Create fields and append them to the new TableDef 
        ' object. This must be done before appending the 
        ' TableDef object to the TableDefs collection of the 
        ' Northwind database.
        .Fields.Append .CreateField("FirstName", dbText)
        .Fields.Append .CreateField("LastName", dbText)
        .Fields.Append .CreateField("Phone", dbText)
        .Fields.Append .CreateField("Notes", dbMemo)

        Debug.Print "Properties of new TableDef object " & _
            "before appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

        ' Append the new TableDef object to the Northwind 
        ' database.
        dbsNorthwind.TableDefs.Append tdfNew

        Debug.Print "Properties of new TableDef object " & _
            "after appending to collection:"

        ' Enumerate Properties collection of new TableDef 
        ' object.
        For Each prpLoop In .Properties
            On Error Resume Next
            If prpLoop <> "" Then Debug.Print "  " & _
              prpLoop.Name & " = " & prpLoop
            On Error GoTo 0
        Next prpLoop

    End With

    ' Delete new TableDef object since this is a 
    ' demonstration.
    dbsNorthwind.TableDefs.Delete "Contacts"

    dbsNorthwind.Close

End Sub

5. So my latest round of suggestions -

• 
  Look at the ADD method for creating the description field
  Check over item (3) to see how to access the description field
  Look at item (4) for some other ideas on creating the table via code
  Ask questions if you still have any!

 

[giffordpinchot]

Boy, that's great service!

Let me run with this and see how it works out. I have a feeling this is just what I need.

Cheers,

Gifford

 

[gromit]

Good luck!