ACC1x: How to List Related Tables in a Database in Version 1.x

ID: Q88653

The information in this article applies to:

SUMMARY

Although you can create relationships among tables in your database, there is no inherent Microsoft Access command to gather this information and present it in a meaningful way. Sometimes this information can be helpful, for instance, to find the source table of a referential integrity violation. This article discusses how you can use a series of queries to gather and relate information you need from the system tables MSysObjects, MSysColumns, and MSysIndexes.


MORE INFORMATION

To gather information and present it in a meaningful way, you can create a query containing two columns. The first column, called Primary.Name, will contain a table name. The second column, called Foreign.Name, will represent the table that the Primary.Name table is related to.

Because the information will be coming from the system tables mentioned above, you must make sure you have access rights to these tables. MSysObjects has these rights by default. To give yourself access rights to MSysIndexes and MSysColumns, follow these steps:

  1. In the Database Window menu, select the View menu, and then choose Options.


  2. Change the Show System Objects setting to Yes, and then choose the OK button.


  3. From the Database Window menu, choose the Permissions command from the Security menu.


  4. In the following dialog box, choose Table for the Object Type prompt, and then choose MSysIndexes for the Object Name prompt.


  5. In the Permissions box at the bottom of the dialog box, select the Full Permissions box.


  6. Click Assign, and then choose the Close button. Repeat steps 4 and 5 for MSysColumns.


  7. From the View menu at the top of the screen, choose Options.


  8. Change Show System Objects to No, and then choose the OK button.


At this point, you can begin building the queries that will retrieve the relationship information. To do so, follow these steps:
  1. In the Database window, choose the Query button, and then choose New. Note that a query design screen appears, along with a dialog box asking for a Table/Query to add to the query. Choose Close so that the query design grid is blank.


  2. From the View menu, choose SQL, and delete any contents that appear in the SQL dialog box.


  3. Enter the following SQL statement into the SQL window:
    
      SELECT DISTINCTROW
         MSysObjects.Name, MSysObjects.Id
      FROM MSysObjects
      WHERE ((MSysObjects.Type=1)
      AND (MSysObjects.Flags Is Null Or MSysObjects.Flags<>2))
      ORDER BY MSysObjects.Name;


  4. Choose OK. Close and save the query as psi User Tables.


  5. Repeat steps 1 through 3, substituting the SQL statement in step 3 with the following SQL statement:
    
      SELECT DISTINCTROW [Tables].Name AS Table,
         MSysColumns.Name AS Field,
         [Tables].Id AS [Primary Id]
      FROM [psi User Tables] AS Tables, MSysColumns, Tables
      INNER JOIN MSysColumns ON [Tables].Id = MSysColumns.ObjectId
      ORDER BY [Tables].Name, MSysColumns.PresentationOrder;


  6. Choose OK, and then close and save the query as psi User Tables Field List.


  7. Repeat steps 1 through 3, substituting the SQL statement in step 3 with the following SQL statement:
    
      SELECT DISTINCTROW Primary.Name, Foreign.Name
      FROM [psi User Tables] AS Primary, MSysIndexes, [psi User Tables]
         AS Foreign, MSysIndexes AS LKeyName, LKeyName
      INNER JOIN MSysIndexes ON LKeyName.Idxid = MSysIndexes.Idxid,
         LKeyName
      INNER JOIN MSysIndexes ON LKeyName.ObjectId = MSysIndexes.ObjectId,
         Primary
      INNER JOIN MSysIndexes ON Primary.Id = MSysIndexes.ObjectId,
         Foreign INNER JOIN MSysIndexes ON Foreign.Id =
         MSysIndexes.ObjectIdReference
      WHERE ((MSysIndexes.ObjectIdReference&lt;&gt;0)
         AND (MSysIndexes.Operation=2)
         AND (LKeyName.Operation=0))
      ORDER BY Primary.Name, Foreign.Name;


  8. Choose OK. Close and save the query as psi Relationships.


  9. To view the Relationships table, highlight the psi Relationships query in the Database window and choose the Open button.


With this query, you can create reports or use the query in any other way that you would normally use a Microsoft Access query to make use of the information.

Another way to display database information is to use the Database Analyzer tool. For more information about this tool, see the PSSKB.TXT file, question 23, or choose the Microsoft Access Q&A icon in the Microsoft Access group in Program Manager. 


Keywords          : kbusage RltOthr 
Version           : 1.0 1.1
Platform          : WINDOWS 
Issue type        : kbhowto

Last Reviewed: March 10, 1999