How to list All Fields for each table in AllTables

[Alibaba2003]

How do I list All Fields for each table in AllTables Collection?
I know this question was posted before.
I just cannot find the thread

Thanks
Tareq

It's Nice to Be Important But It's more Important to BE Nice

 

[PHV]

Either use the menu Tools -> Analyze -> Documenter
Or play in VBA with the TableDefs and DAO.Fields collections.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[vpelleri]

Here is an example of how to do it using code with ADO.

Private Sub DataDictionary()
    Dim cat As ADOX.Catalog
    Dim col As ADOX.Column
    Dim tbls As ADOX.Tables
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection
    Set tbls = cat.Tables
    
        For Each tbl In tbls
            Debug.Print tbl.Name
            
            For Each col In tbl.Columns
                Debug.Print col.Name
            Next col
        
        Next tbl    
End Sub

Vince

 

[vpelleri]

I forgot to mention that to use ADOX you will need to include the Microsoft ADO ext. 2.5 for DDL and Security reference under Tools/References... in the VBA coding environment.
Vince

 

[Alibaba2003]

Yes but how do we access the description property of each column. Assuming i want to print as well the column Description. Thanks

It's Nice to Be Important But It's more Important to BE Nice

 

[vpelleri]

Add this to the code I posted above.

Dim prp As ADOX.Property

For Each prp In col.Properties
     If prp.Name = "Description" Then
          Debug.Print prp.Value
     End If
Next prp

Vince

 

[PHV]

You may also do a google search for speed ferret

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Fiat77]

But can Speed ferret show dependencies for more than one object

 

[Alibaba2003]

Great. Now I have all of the information stored in a table and i want to get that information to update a database with the same structure. the main purpose is to update the description of the columns or fields. Here is what i wrote:

Function UpdateFieldDefinition()
Dim cat As ADOX.Catalog
Dim col As ADOX.Column
Dim tbls As ADOX.Tables
Dim tbl As ADOX.Table
Dim dbsConn As New ADODB.Connection
Dim rUpdateTables As New ADODB.Recordset

Set dbsConn = CurrentProject.Connection

rUpdateTables.Open "TableFieldDefinition", dbsConn, adOpenKeyset, adLockOptimistic

Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set tbls = cat.Tables

Do While Not rUpdateTables.EOF
Set tbl = rUpdateTables!TableName ' i want to make current field value the current table
Set col = rUpdateTables!FieldName '?? set the field name to current column name
col.Properties("Description") = rUpdateTables!FieldDescription 'set column description property to current description field
rUpdateTables.MoveNext
Loop



End Function

Now i dont know how to tell the program to convert the table and the column names to Table and column? Please commented lines in code. Thanks

It's Nice to Be Important But It's more Important to BE Nice