The information in this article applies to:
- Microsoft Access versions 1.0, 1.1
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:
- In the Database Window menu, select the View menu, and then choose
Options.
- Change the Show System Objects setting to Yes, and then choose the
OK button.
- From the Database Window menu, choose the Permissions command from
the Security menu.
- In the following dialog box, choose Table for the Object Type
prompt, and then choose MSysIndexes for the Object Name prompt.
- In the Permissions box at the bottom of the dialog box, select the
Full Permissions box.
- Click Assign, and then choose the Close button. Repeat steps 4 and
5 for MSysColumns.
- From the View menu at the top of the screen, choose Options.
- 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:
- 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.
- From the View menu, choose SQL, and delete any contents that appear in
the SQL dialog box.
- 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;
- Choose OK. Close and save the query as psi User Tables.
- 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;
- Choose OK, and then close and save the query as psi User Tables
Field List.
- 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<>0)
AND (MSysIndexes.Operation=2)
AND (LKeyName.Operation=0))
ORDER BY Primary.Name, Foreign.Name;
- Choose OK. Close and save the query as psi Relationships.
- 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 : RltOthr kbusage
Version : 1.0 1.1
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto
|