Report: List of Tables and their field names

[Guest]

I have a massive database that I inherited from someone who is no longer with
the company and need to produce some reports.

My problem right now is figuring out which tables have the fields I need.
If I could get a list of all the tables and the fields each contains I could
figure it out I think.

Is there a way to get a report with this information? I have tried
searching these forums and have not found anything that really works the way
I need it to.

Can you help?

 

[Guest]

I ran that, but it isn't in a really good format to be able to do much with
the data. My end goal is to have some kind of a worksheet that lists the
fields and which tables they are each in.

Thanks for the note.

 

[Allen Browne]

You can get a list of the tables and fields from the built-in documenter:
Tools | Analyze | Documenter

Jeff Conrad has a better documenter here:
http://www.accessmvp.com/JConrad/accessjunkie/csdtools.html

What I personally do is to modify the way the relationship diagram prints so
it displays the field types, sizes, indexes, and flags fields that are
required or have validation rules. See:
Relationship Report with extended field information
at:
http://allenbrowne.com/AppRelReport.html
I use an A3 printer, but Stephen Lebans has a utility that allows to you
save multiple relationship views. (It's linked from the article above.

Finally, if you want to do it yourself, this query will give it to you:
SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*")
AND ([Name] Not Like "MSys*"))
ORDER BY MsysObjects.Name;
Attached tables can be type 4, 6, or 8.
You can then OpenRecordset on this, and loop through the fields like this:
http://allenbrowne.com/func-06.html

 

[George Nicholson]

This fills a table with FieldName, Source Type (Query or Table) and
SourceName for the CurrentDB. It could easily be expanded to provide
additional information for each field.

If you want a more heavy-duty cross ref tool, I use Access Analyzer a lot.
http://fmsinc.com/

Private Sub ListAllFields()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim qdf As DAO.QueryDef
Dim fld As DAO.Field
Dim strSQL As String

' ** Assumes existence of Table tblFieldList with Fields: FieldName,
SourceName, SourceType **

Set db = CurrentDb
strSQL = "DELETE * FROM tblFieldList"
db.Execute strSQL, dbFailOnError

For Each tdf In db.TableDefs
For Each fld In tdf.Fields
strSQL = "INSERT INTO tblFieldList (FieldName, SourceName,
SourceType) Values ('" & fld.Name & "', '" & tdf.Name & "', 'Table')"
db.Execute strSQL, dbFailOnError
Next fld
Next tdf

For Each qdf In db.QueryDefs
For Each fld In qdf.Fields
strSQL = "INSERT INTO tblFieldList (FieldName, SourceName,
SourceType) values ('" & fld.Name & "', '" & qdf.Name & "', 'Query')"
db.Execute strSQL, dbFailOnError
Next fld
Next qdf
MsgBox "Done"

End Sub


HTH,