Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Location
    USA
    Posts
    3

    Unanswered: 250 tables in 30 .mdb files - help!

    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?

    -Tony
    Last edited by aboyd; 08-08-06 at 17:04.

  2. #2
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  3. #3
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    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
    Next fld
    Next tbl

    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:
    Code:
    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.

    have fun,
    tc

  4. #4
    Join Date
    Aug 2006
    Location
    USA
    Posts
    3
    Wanted to say thanks!

    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.

    -Tony

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •