If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report: List of Tables and their field names
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? |
#2
|
|||
|
|||
Report: List of Tables and their field names
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. "Klatuu" wrote: Use the Access documenter. It will give you whatever level of detail you want. Tools, Analyze, Documenter -- Dave Hargis, Microsoft Access MVP "porter444" wrote: 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? |
#3
|
|||
|
|||
Report: List of Tables and their field names
You can get a list of the tables and fields from the built-in documenter:
Tools | Analyze | Documenter Jeff Conrad has a better documenter he http://www.accessmvp.com/JConrad/acc.../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 -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "porter444" wrote in message ... 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? |
#4
|
|||
|
|||
Report: List of Tables and their field names
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, "porter444" wrote in message ... 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. "Klatuu" wrote: Use the Access documenter. It will give you whatever level of detail you want. Tools, Analyze, Documenter -- Dave Hargis, Microsoft Access MVP "porter444" wrote: 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? |
Thread Tools | |
Display Modes | |
|
|