listing fields

[buggy2]

I have listed the table names and the the field name for each of the tables out using msgbox(). I have also created a new table def and i was wondering how i could go about writing this data into the table i created in the form:

table1:field 1
table1:field 2
: :
: :
table1:Field n
table2:Field 1
: :
tablen:Field n

Here is the code i a using:

Sub list_fields()
Dim list_field As DAO.Field
Dim tbl As DAO.TableDef
Dim db As DAO.Database

Set db = CurrentDb
Set tbl = db.CreateTableDef("tbl List Fields")

For Each tbl In DBEngine.Workspaces(0).Databases(0).TableDefs
MsgBox (tbl.Name)

For Each list_field In DBEngine.Workspaces(0).Databases(0).TableDefs("tbl_value").Fields
MsgBox (loopy.Name)
Next

Next

End Sub

 

[Russell Hauf]

Try something like this:

Sub WriteTableFieldNamesToAnotherTable()
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    
    Set db = CurrentDb
    
    Set tdf = db.CreateTableDef("tblListFields")
    With tdf
        .Fields.Append .CreateField("fldTableName", dbText)
        .Fields.Append .CreateField("fldFieldName", dbText)
    End With
    
    db.TableDefs.Append tdf
    Set rst = db.OpenRecordset(tdf.Name)
    Set tdf = Nothing
    
    For Each tdf In db.TableDefs
        For Each fld In tdf.Fields
            With rst
                .AddNew
                .Fields("fldTableName") = tdf.Name
                .Fields("fldFieldName") = fld.Name
                .Update
            End With
        Next fld
    Next tdf
            
    Set rst = Nothing
    
End Sub

HTH,

Russell

 

[buggy2]

thanks

 

[foxhound]

listing fields, field data type, and description

How can this code be adapted to also list the field's data type and description?

 

[foxhound]

Thanks but I figured it out

Sub WriteTableFieldNamesToAnotherTable()
    Dim db As DAO.Database
    Dim fld As DAO.Field
    Dim tdf As DAO.TableDef
    Dim rst As DAO.Recordset
    Dim strTblName As String
    
    Set db = CurrentDb
    
        strTblName = "tblListFields"
        DoCmd.DeleteObject acTable, strTblName
        
    Set tdf = db.CreateTableDef("tblListFields")
    With tdf
        .Fields.Append .CreateField("fldTableName", dbText)
        .Fields.Append .CreateField("fldFieldName", dbText)
        .Fields.Append .CreateField("fldDescription", dbText)
    End With
    
    db.TableDefs.Append tdf
    Set rst = db.OpenRecordset(tdf.NAME)
    Set tdf = Nothing
    
    For Each tdf In db.TableDefs
    
    On Error GoTo Err_testing
        
        For Each fld In tdf.Fields
            With rst
                .AddNew
                .Fields("fldTableName") = tdf.NAME
                .Fields("fldFieldName") = fld.NAME
                .Fields("fldDescription") = fld.Properties("Description")
                .Update
            End With
        Next fld
    Next tdf

Exit_testing:
    Exit Sub
    
Err_testing:
    If Err.Number = 3270 Then
        '3270 is the error that is thrown for missing properties...
        Resume Next
    Else
        MsgBox "Error: " & Err.Number & ": " & Err.Description
        Resume Exit_testing
    End If
    
    Set rst = Nothing
    
End Sub