I have inherited 250 tables in 30 .mdb files. It's not well-organized -- the .mdb files were found only by doing a search in a drive containing roughly 10,000 .asp files in various directories.
The problem is that I am not good enough with MS Access to visualize this stuff. I opened one .mdb file, right-clicked on a table, selected "Design View" and tried to print that out, but print was disabled. And even if I could get it to print, I don't want to do that 250 times.
So I'm wondering if anyone can tell me what features MS-Access has for scouring/summarizing it's own database structures? Or, what freeware exists to explore such things? I need visualization help, and reports showing what tables are in each database, as well as the field names and type/relationship. Any ideas, anyone?
If you are lucky enough that someone setup Relationships, you can print those (Tools/Relationships then File/Print Relationships). Otherwise there is the Documenter (Tools/Analyze/Documenter). Not real useful in my opinion, but it will document the schema. Other than that I don't know of any freeware.
If you are a bit snazy with VBA, then you can use a few nested loops:
Dim dbf As DAO.Database
Dim tbl As DAO.TableDef
Dim fld As DAO.Field
Set dbf = OpenDatabase(strFileName)
For Each tbl in dbf.Tables
For Each fld in tbl.Fields
strText = fld.Name & ", " & fld.what ever else you want etc.
' put strText somewhere
Note, this is "Air Code" but you get the idea.
You can do the same thing on Forms, Queries and Reports to get some basic info about those as well. Personally, I'd store the results in a datatable since you will undoubtedly want to run queries against it.
Also, you will want to get info about each table - especially the connection strings.
If you go into DOS (a "Command Window" for the yougsters out there) and type:
Dir *.mdb /s >TheList.txt
you will get a complete directory listing of all the mdb files in that directory and all sub diretories in a text file, which you can massage in Excel to give you a file list so y ou can automate the process.
The tip about the Documenter was good -- I didn't have that feature installed, but managed to find the CD and get it added in just fine. Also, really appreciate the pseudocode, it's excellent for helping me to have a tangible example of how this works. Thanks again.