list of table names and fields

[dlb1228]

i got this code off of this site to give me a list of the table names and
fields in my database. When i run the code in the immediate window it starts
maybe in the middle of my tables and lists them, it doesnt give me all my
tables. Any idea why? and is there a way to make a table of this data?

Public Sub GetFieldNames()
Dim dbs As Database, tdf As TableDef, fld As Field
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
Debug.Print tdf.Name
For Each fld In tdf.Fields
Debug.Print " " & fld.Name
Next fld
End If
Next tdf
Set dbs = Nothing
End Sub

 

[Graham R Seach]

I can't see any reason why that code wouldn't return ALL your tables, except
of course, for system tables whose names begin with "MSys". If they're the
ones you're talking about, then remove the following line:
If Left(tdf.Name, 4) <> "MSYS" Then
....and it's associated End If statement.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

 

[dlb1228]

it starts with say the 6 table but before the 6th table it prints well it
looks like 2 fields could there be something with the immediate window
that is not allowing me to see the other files

 

[Graham R Seach]

No, but it might be that you have so many tables, the Immediate Window can't
display them all. You see, the Immediate Window has a cache limit; once you
reach that limit, it discards things on a first-in-first-dropped basis.

Try apending the data to a table.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
---------------------------

 

[dlb1228]

how do i append the data to a table thats what i would like to do im
hitting F5 to run it in the window is there something i type in there to
append it to a table?

 

[Klatuu]

First, create a table to keep the info in. I will call it tblMyDb. Then you
need two fields. TABLE_NAME and FIELD_NAME.

Public Sub GetFieldNames()
Dim dbs As Database, tdf As TableDef, fld As Field
Dim rst As RecordSet

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tblMyDb", dbOpenDynaset)

'Remove the old data
dbs.Execute("DELETE * FROM MyDb;"), dbFailOnError)

For Each tdf In dbs.TableDefs
If Left(tdf.Name, 4) <> "MSYS" Then
For Each fld In tdf.Fields
With rst
.AddNew
![TABLE_NAME] = tdf.Name
![FIELD_NAME] = fld.Name
.Update
End With
Next fld
End If
Next tdf
rst.Close
Set rst = Nothing
Set dbs = Nothing
End Sub