List Tables and Field Names

[Purdue2479]

I am trying to use the below code to list in a table all the tables and there associated field names in the database excluding the system tables. What I am having trouble with is excluding the system tables. I'm not sure how to modify the code to do this. Any suggestions would be appreciated.

Private Sub showtdf()

Dim db As Database
Dim tdf As TableDef
Dim x As Integer
Dim tbl_Name As String
Dim tbl_Field As String

DoCmd.SetWarnings True
Set db = CurrentDb

 If Left(tbl_Name, 4) <> "MSys" Then
For Each tdf In db.TableDefs
 'Debug.Print tdf.Name,
 'Debug.Print tdf.Fields.Count
 'Debug.Print tdf.Fields(x).Name,
   tbl_Name = tdf.Name
  
  For x = 0 To tdf.Fields.Count - 1
  tbl_Field = tdf.Fields(x).Name
 
 DoCmd.RunSQL "INSERT INTO Table1 ([Table Name],[Table Field] ) Values ('" & tbl_Name & "','" & tbl_Field & "');"

 
   Next x
 
Next tdf


End If
DoCmd.SetWarnings True
End Sub

 

[Purdue2479]

After rearranging the code I finally got it to work. Not sure this is the best way to do it, but it works.

Private Sub showtdf()

Dim db As Database
Dim tdf As TableDef
Dim x As Integer
Dim tbl_Name As String
Dim tbl_Field As String

DoCmd.SetWarnings False
Set db = CurrentDb

  
For Each tdf In db.TableDefs
 'Debug.Print tdf.Name,
 'Debug.Print tdf.Fields.Count
 'Debug.Print tdf.Fields(x).Name,
  tbl_Name = tdf.Name
    
  If Left(tbl_Name, 4) <> "MSys" Then
  For x = 0 To tdf.Fields.Count - 1
  tbl_Field = tdf.Fields(x).Name
 
 DoCmd.RunSQL "INSERT INTO Table1 ([Table Name],[Table Field] ) Values ('" & tbl_Name & "','" & tbl_Field & "');"

 
   Next x
 End If

Next tdf


DoCmd.SetWarnings True
End Sub

 

[PeterOC]

Hi Purdue, I've been trying to figure out how to do this myself (I'm just starting to get into TableDefs) so you're code was a big help. Cheers!