Microsoft Access: Applications and Utilities

Provided by Allen Browne. February 2006.  Last updated April 2010.

Relationship Report with extended field information

Notes:

• In Access 2007 and later, this utility does not draw the tables correctly if they contain complex data types (attachments or multi-value fields.)
• Access 2007 cannot show the relationships for an ACCDB that contains tables with calculated fields, and this utility does not solve that incompatibility.

If you develop in Access or examine other people's databases, you probably use the Relationships diagram to track the big picture - how the database fits together. You print the relationship diagram, and probably write extra information beside the fields: the primary key, which fields are required, what type of field, the number of characters it can contain, and so on.

But Access knows all that information! Why not get it to add the codes beside each field instead of doing it by hand? That is what this utility does: print codes beside each field on the relationship diagram, to explain the nature of the fields.

The example below shows the relationship diagram for a copy of Northwind. In the Customers table for example, CustomerID is a Text type field, 5 characters, Primary key. CustomerName is a Text field, 40 characters, and Required. This copy has a Yes/No field named Inactive (not in the original Northwind), and there are 91 records in this table.

If that sounds useful, copy the code from the link below, and add it to any database (Access 2000 and later).

One more suggestion: if you have a large database with too many tables to fit on screen, Stephen Lebans has a utility to save multiple relationship views, and load them again later. With this extended printout combined with his multiple relationships view, you will be able to stay on top of your database.

Installing in your database

Steps:

1. Create a new module: In Access 2000 - 2003, click on the Modules tab of the Database window and click New. In Access 2007, click the Create ribbon, and choose Module in the Other group (rightmost icon.) Access opens a new code window.
2. Copy the code in this link, and paste into the new module.
3. Check that the code compiles, by choosing Compile on the Debug menu.
4. Save the module with a name such as "ajbRelReport".

If step 3 fails with a compile error in Access 2000 or 2002, choose References from the Tools menu, and check the box beside "Microsoft DAO 3.6 Library." (See References for more information.)

You can now run the code by opening the Immediate Window (Ctrl+G), and entering:
    ? RelReport()

Or, you can place a button on a form, and set its On Click property to:
    =RelReport()

In Access 2002 or 2003, you can open the Relationships window, and customize its toolbar by adding a new button and setting its On Action property to:
    =RelReport()
Now you can open this extended report directly from the toolbar, in any database where you have this code.

Result codes

Customize the routines so they return codes that are meaningful for you. As it stands, the first thing beside each field indicates the field type, then any indexes the field is involved in, and lastly some key properties that might be important to know. This table summarizes the three types of code shown beside your fields:

Why focus on these particular field properties? They are the ones that affect the integrity of the data:

• Allow Zero Length should be set to No for almost every text field, to avoid confusing users, and so you do not have to constantly test for both null and zero-length strings. Unfortunately, Access sets this property inconsistently, depending on the version of Access and how the field is created (interface, DDL, DAO, ADOX.)
• The Required property is a crucial to ensure integrity at the engine-level, especially on foreign keys.
• A Validation Rule provides range-checking, and some developers use it in preference to Required because it facilitates a custom message (Validation Text.)
• The Default Value is less important than other properties, but we include it here as there is a bug in Access where it will wrongly assigns the Default Value to a lookup table field in a new record in a multi-table query, causing the insert for the main table to fail.

How it works

You can use the code without having to understand how it works. Read this section in conjunction with the VBA code.

The Relationships report (in Access 2000 and later) generates a list box for each table, and draws lines to show the relations. The RowSource of each list box is a value list - just a concatenated list of field names. This utility triggers the built-in relationships report, switches it to design view, and replaces the RowSource string with a more detailed list.

The main entry point is the RelReport() function. It calls OpenRelReport() to get Access to create the relationships report. In Access 2002 and later, this is just a matter of opening the relationships window, asking it to print the relationships and then switching to design view with just these three lines:
    RunCommand acCmdRelationships
    RunCommand acCmdPrintRelationships
    RunCommand acCmdDesignView
Unfortunately, the middle line does not work in Access 2000, so we use SysCmd() to check the version and take a different approach for Access 2000. If the Relationships window has focus, Alt+FR will trigger File | Print Relationships, so we fudge that with SendKeys. As always, SendKeys is a kludge, and it does not work unless the Relationships window has focus. If it was already open at the time our code called RunCommand acCmdRelationships, the already open window does not take focus, and we receive error 2046. The error handling branches for Access 2000, and asks the user to close the Relationships window so that it will have focus when this routine opens it. (That's the reason you cannot use the toolbar button on the Relationships toolbar in the old version.)

OpenRelReport() then returns the name of the last report that was opened, i.e. Reports(Reports.Count - 1).Name. Back in our main function, we can now loop through the controls on that report, and find the list boxes. The attached label of the list box has the name of the table in its Caption. The attached label is Controls(0), so we will set a TableDef to the name in that caption, and we can get all the field information we need. Unfortunately, this fails if the name of the table in the Relationships window is an alias. This happens when you have multiple copies of a table in the window - for example so you can join a table to itself, or create multiple joins between a pair of tables. If we strike an alias, we are happy to just leave it displaying the default information, since there is probably another copy of the table that will have our extended details anyway. Since we anticipate errors on setting the TableDef, we call a separate routine to run that line. We pass TdfSetOk() the current database (more efficient than reopening it every call), an uninitialized TableDef variable so the subroutine can set it and pass it back, the list box (so it can get the caption from the attached label, which could theoretically error as well if it had no attached label), and a string to append any error message to. If the function succeeds, it returns True, so our main routine tests that before operating on the returned TableDef variable.

Back in the main routine, we now pass the initialized TableDef variable to DescribeFields(). This function will return a string of delimited values, suitable to use as the RowSource of the list box.

DescribeFields() loops through the Fields collection of the TableDef, examining each one, and building up the string to return. We skip fields that have the dbSystemField attribute set (used for resolving conflicts in replicated databases) since Access skips these in the Relationships report, and we need the lines between tables to join up to the correct field. The Select Case block examines the field's Type, and assigns our codes. Access uses the field's Attributes to distinguish between an AutoNumber and a Long Integer, and between a Memo and a Hyperlink, so we examine those in the appropriate cases.

Before moving on to the next field in the table, the code calls DescribeIndexField() to get the characters we want to display regarding the index. Now, indexes occur on tables not fields, and an index can cover multiple fields. For each field, therefore, we loop through all the indexes of the table to see it it contains this field name in its Fields collection. If it does, we return "P" if the index's Primary property is true, "U" if the index's Unique property is true, or just "I" if the field is indexed. If the field is not the first one in the index, it can only be used in conjunction with the previous index field(s), so we return a lower case character to indicate this condition.

In this way, DescribeFields() concatenates the field names and codes, in quotes, separated by semi-colons - just what a value-list list box needs. We have a trailing carriage return at the end, so we can just tack on a record count in quotes, putting to good use the space that Access leaves below the fields in each list box on the Relationships report.

Our main function - RelReport() accepts this string, assigns it to the RowSource of the list box, and loops to the next control on the report until all the tables (list boxes) have been processed.

Now all the information is ready, we try to offer the report in the best condition we can. There never seems to be enough room to fit all the tables on the report, so we shrink the unnecessary report footer section to zero height, open the report in preview mode, change the orientation to landscape, and reduce the report's margins to half an inch. RelReport() takes an optional parameter to skip this reorientation if you find it undesirable. Again, Access 2000 lacks the Printer object, so SetMarginsAndOrientation() just skips this code in the old version. If we passed a Report type object to this function, Access 2000 would complain that the Printer does not apply, and the code would fail to compile. We fool it by passing something more generic: an Object instead of a Report. The routine now compiles but is not execute in A2000.

If you read this far, hopefully some of these techniques will prove helpful in other contexts as well.