Tables & Queries:
Document a Table

Title: Jan's Illustrated Computer Literacy 101
Did you want: Working with Databases: Access 2007, 2010, 2013, 2016

After creating several tables, do you remember the design choices you made and why?

Good documentation is important. You may not be the one who has to maintain the database later. After a month or two have passed and after a few other projects have occupied your mind, you may get confused yourself about how all the parts fit together.

You can print a table's datasheet but you cannot print the Table Design View. So annoying! Access has several features that can help you document your tables and other objects but you will have to put the pieces together in your head yourself.

  • Relationships: The Relationships window can be printed!

  • Documenter: This handy feature is not part of the default installation. It produces a detailed list of the features of your tables, including all of the properties of each field. This is a big mess... until you need what's in it! Then it is a gold mine!
      Tools | Analyze | Documenter

  • Icon: Access 2003 Object Dependencies task pane: In Access 2003 you can see what objects depend on other objects. This is very helpful when you are thinking about revising or deleting an object. It takes a bit of practice, however, to understand the tree of dependencies.

Where you are:
JegsWorks > Lessons > Databases

Before you start...

Project 1: Intro

Project 2: Access Basics

Project 3: Tables & Queries Arrow: subtopic open
    Designing Tables Arrow: subtopic open
    Icon: StepDesign Tables
    Icon: StepFormat Fields
    Icon: StepIndexes
    Icon: StepValidation
    Icon: StepRedesign Table
    Icon: StepDocument a Table
    Designing QueriesTo subtopics
    ExercisesTo subtopics

Project 4: Forms & Reports


Icon: Step-by-Step 

Step-by-Step: Document a Table

 Icon: Step-by-Step

What you will learn:

to print Relationships
to print a table datasheet
to document a table with Documenter
to view dependencies

Start with:  Class disk, projects.mdb open.

Print Relationships

  1. Display the Relationships by clicking the Relationships button Button: Relationships.

    Icon: Trouble Problem: Don't see the Relationships button
           Why: You are not in the correct view.
           Solution: Switch to Design View or Database Window.

  2. Relationships: Print Preview with 3 tables From the menu select  File | Print Relationships...
    A new window appears which displays the Print Preview of the Relationships window.
    You cannot add a header or footer or make any changes to this page.

  3. Icon: Print Click on the Print button Button: Print on the toolbar to print this report.

  4. Close the print preview window without saving the report.

  5. Close the Relationships window.

Print Table Datasheet

  1. Table Datasheet View: ProjectsOpen the table Projects in Datasheet View.
    The fields ProjectName and ProjectDescription are too narrow to show everything in them. This is exactly how the table will print!
    You need to adjust the column and row sizes.

  2. Table Datasheet View: Adjusted row heights and column widthsDrag the column for ProjectName wide enough to see all of the names.

  3. Drag the ProjectDescription column wider and then drag the row to make it taller. All rows will be the same height!!

  4. Print Preview: Table datasheet after adjustmentsOpen Print Preview again. Better!

    The subdatasheets will not print even if they were open in Table Datasheet View!


    Dialog: Page Setup - Print Headings selectedIcon: TroubleProblem: Column headings do not show in the print preview.
    Click on the Setup... button. On the Margins tab, check the box for Print Headings.

  5. Icon: Print Print  by clicking the Print button Button: Print.

    Unfortunately, you do not have much control over table print-outs in Access. That's why we will be learning about Reports later!

  6. Close the table.

Use Documenter

The Documenter report includes a LOT of information. Exactly what you see depends on what properties have been set for the table and its fields.

  1. Menu: Tools | Analyze | DocumenterFrom the menu, select  Tools |  Analyze  | Documenter .
    The Documenter window opens.
    Icon: TroubleProblem: Documenter was not installed:
    The default installation of Access does not include this useful feature. When you click on the Documenter command, Office offers to install it. It will walk you through the process. You will need the installation CD.

  2. Dialog: Documenter - ProjectsOn the Tables tab, check the box for Projects and click on OK.
    Access creates a report and opens it in Print Preview.

    Message: Must close table first. Close now?Icon: Trouble Problem: Message appears instead.
    If the object that you want to document is still open, it must be closed first. Just click on Yes.

  3. Print Preview: Documenter report - page 1View each page of the 3-page report.
    Eek! What a mess! There is a lot more information here than you probably want to deal with!

    Features that do not have settings, like Format property or Input Mask property, are not shown.

    You can scroll within a page but scrolling does not move you to the next page. You must use the arrow buttons at the bottom of the window to change pages.

    Toolbar: Navigate pages

    Icon: TroubleProblem: Cannot tell how many pages there are.
    Nothing in the window tells you how many pages there are in the report.
    Solution: Click on the LastPage button Button: Last Page. Once that last page displays, you can see in its page number in the navigation box. That's how many pages are in the report.
    Complication: Access must format all of the pages before it can show you the last page. For long or complex reports, this can take a long time! If your computer is underpowered, the whole program can lock up or generate an error.

  4. Icon: Print Print the report by clicking the Print button Button: Print on the toolbar.

  5. Close the report.

  6. Find the following information in the report.   If your instructor wishes, highlight or circle the answers on the print-out and label with the matching letter. Turn your marked report in for grading.

    1. Name of the table

    2. Location of the database file

    3. Date the table was created

    4. Date of the print-out

    5. Number of records in the table

    6. Size of the ProjectName field

    7. Type of the ProjectDescription field

    8. Table(s) related to the table

    9. Are cascading deletes and updates allowed?

    10. Type of relationship

    11. How many indexes are there?

  7. Create a Documenter report on the the table Staff. This table has more fields than the Projects table, so the report will be 10 pages! Do not print unless your instructor tells you to.

  8. Find the following information in the report:
    Either mark your print-out or create a document to list your answers.

    1. Name of the table

    2. Location of the database file

    3. Date the table was created

    4. Date of the print-out

    5. Number of records in the table

    6. Table(s) related to the table

    7. Are cascading deletes and updates allowed?
      "Not enforced" means that referential integrity is not enforced and therefore no cascades are allowed.

    8. Type of relationship

    9. How many indexes are there?

    10. What field(s) have a Format property set?

    11. What field(s) have an Input Mask?

    12. What field(s) have a validation rule and what is the rule?

View Dependencies

This tree view of dependencies is useful when you are making changes or deletions to objects in your database. If you look at the dependencies first, you can avoid embarrassing and time-consuming mistakes. Unfortunately there is no convenient way to print out this pane.

  1. Task Pane: Object Dependencies - for ProejctStaff tableIn the Database Window, right click on the table ProjectStaff.

  2. From the popup menu select  Object Dependencies....
    The Object Dependencies task pane opens at the right. It shows the name of the object.
    There are two dependency trees - "Objects that I depend on" (the default view) and "Objects that depend on me". In this case they are the same... for the moment.
    The ProjectStaff table has a relationship with both the Project table and the Staff table. As you add other tables and queries, other objects may appear.

    Icon: TroubleProblem: No dependencies shown
    Enable "Tracking name AutoCorrect info" in  Tools | Options... | General

  3. Click on a + button in the pane to expand the dependencies tree. These can be nested up to 8 deep but you can only navigate through 4 levels. When expanded, it looks very confusing because the same object will often appear in several places. Dependencies can be a tangle of relationships that loop back on themselves! The key facts are simple. Objects that are dependent on each other cannot be deleted arbitrarily! It takes careful thinking.

TipPreferred Method: The right click menu is the preferred way to show the Object Dependencies for an object. With this method it is clear to Access that you want to see dependencies for the selected object. Other methods may not make Access forget what the last object was! So frustrating!!