part of a Relationship Diagram in Access database

Home Articles Code Tools

Show Relationship and Index information for Access

Show information about relationships in an Access database such as parent and child table names, fields that are related, how many columns there are, relationship type, and relationship name.

When a relationship with referential integrity (RI) is created, an index is needed in the table with the foreign key, since the primary or parent table must already have an index. If Access creates an index, it will be hidden, so you won't see it when you look at indexes for a table in the user interface. This tool shows all indexes for any table, including hidden indexes that Access creates behind-the-scenes.

The relationships diagram is a great visual to show what's in your database, if you've laid it out well.

With this tool, you can see a little deeper. Free with open source code that you can modify and learn from.

List SQL Server Foreign Keys

https://www.osel.com.au/access-to-sql-server-migration/list-sql-server-foreign-keys/ by Kent Gorrell

relationship and index information for tables in an Access database

Download

Objects to import

s4p_objects_RelationshipsIndexes__ACCDB.zip

Form: fs4p_RELATIONSHIPs_INDEXes_Menu
Module: mod_s4p_rel_GetRelationshipType

The form has options to:

see a list of all relationships in an Access database
see a list of parent/child/both relationships for a particular table
go to a table design
open the Relationship Diagram
pick from list of tables in the database
show index information for any table

The module has a function to return a relationship type (with optional parameter to abbreviate) from the mysterious grbit field in MSysRelationships.

Queries that don't exist will be created.

Objects to import AND sample database for testing

RelationshipsIndexes_plus_MyContacts_s4p__ACCDB.zip

This download has MyContacts +form+module for tool so you have something to analyze to see how it works.

When you run the tool, it will create needed queries.

Unblock downloads

If you have trouble with the downloads, you may need to unblock the ZIP file, aka remove Mark of the Web, before extracting the file. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Reference

Microsoft Learn

Help: And operator

MsAccessGurus

SQL for Query to list Relationships in Access database + VBA function to GetRelationshipType

Download Access database with source code for personal contact mangement MyContacts

Goto Top

Backstory

Since Access uses an index on each side of a relationship when referencial integrity is enforced, it (usually) automatically creates a hidden index in the child table.

Therefore, it makes sense to look at indexes when you're diving into relationships.

This is a somewhat shallow dive, as far as relationships go. There is much more when you correlate relationship fields with the table they come from ... data type, size, description, etc.

To see objects in your database this tool uses or created, filter Naviagtion pane for: s4p_rel

Share with others

Here's the link for this page in case you want to copy it and share it with someone:

https://msaccessgurus.com/tool/RelationshipsIndexes.htm

or in old browsers:
http://www.msaccessgurus.com/tool/RelationshipsIndexes.htm

Good Relationships

Data structure is the most important thing to get right! With good structure and relationships, the rest is downhill.

the simplest way is best, but usually the hardest to see

~ crystal