Access Add-in to Save and Restore Relationship Layout

Quick Jump

Download

Download ACCDA

Download the zipped ACCDA with source code to be installed as an Access add-in from Kent's GitHub repository.

https://github.com/KentGorrell/oRelationLayout

From the main page, click on the dropdown arrow for and choose Download ZIP

Remember to UNBLOCK files you download to remove the Mark of the Web if it is set. Here are steps to do that: https://msaccessgurus.com/MOTW_Unblock.htm

Notes

SAVE a relationship layout

When you have a good layout that you want to save, in the Relationship window:

1. Position scrollbars so you see the left-most table and top-most table.
2. Use the add-in to save the layout with a name that you devise.
   This makes records in the oSysRelationLayout table, which will be created in your CurrentDb if it's not already there.

RESTORE — 3 lessons for this really awesome tool

1. Make sure all tables to be adjusted (restored) are on the relationships diagram. It cannot reorganize any tables not on the diagram already.
   The tool will still work, but tables not there will be ignored.
2. unselect any table in the relationships diagram before you Restore.
   To do this, click on a "gray" area of diagram that isn't over another object so any yellow highlight around a table goes away. This stops tables from going off the diagram where you can't see them when you Restore.
   If you forget to do this and tables go where you can't see them, do this step and Restore again.
3. Assuming step 2 is complete, the scroll bars for the vertical and horizontal position (wherever they are at) determine the top-left starting point (move them appropriately when you Save and Restore).

Clear the Layout of the Relationships Diagram

1. Select all tables, Ctrl-A
2. Press the DELete key on your keyboard

Show All Relationships

Lesson #1 is that all tables to be reorganized must be on the digram. What's the easiest way to do this?

1. Move scrollbars so the top-left of what you see is where you want the added table windows to go
2. Right-click on canvas and choose Show All Relationships
   
   this puts a bunch of boxes with tables on the diagram without any logical positioning.

• If tables are already on the diagram, they aren't added again.
• If you're doing this to Restore, it doesn't matter if tables are on top of each other — they just have to be there.
• If your saved layout contains any tables or queries without relationships, add them manually if you want them to be positioned.

Restore relationships to another database

It's a good idea to occasionally make a blank database with a good name and import all objects into it. Big problem is that you lose the Relationships diagram layout — now you don't have to.

1. Save the good layout using the add-in in your working database.

New Database

2. Make a blank database
3. Import all the objects from your working database (including the oSysRelationLayout table)
4. VBA: Add any needed references (Tools, References)
5. Compile (,fix) and save the VBA code.

Restore the layout (to another database)

Make sure you have (import) the oSysRelationLayout table with the saved relationship layout(s) that you want to restore.

6. Open Relationships diagram (in your new database)
7. Position scrollbars if desired, and perhaps also Clear the Layout? if you don't care to keep what's there, if anything.
8. Show All Relationships to add every table with a relationship, or manually add the tables that you want.
9. Position scrollbars to the top-left of where you want diagram to be restored.
   Perhaps you want a little more space above and/or to the left?
10. Use the add-in to restore the layout with the name you saved.

Quick way to Restore a diagram with only some of the tables

If you don't want to drag tables to the diagram individually:

1. Clear the Layout unless you have something good
2. move scrollbars a couple screens left and/or down — to some spot that won't interfere with the layout you will restore.
3. Show All Relationships and maybe Restore full layout down and over there

Then

4. move scrollbars to upper left unless you have a reason to put them somewhere else.
5. Use the add-in to Restore the layout with the name you saved.
6. Lasso and delete tables outside (to the right and below?) the relevant area or just leave them there. Deleting tables (Hide) from the relationships diagram only hides them from you seeing them. The relationships aren't gone.

Reference

This tool, written by Stephen Lebans and updated to run as an add-in in both Access 32 and 64-bit on Kent's GitHub repository

https://github.com/KentGorrell/oRelationLayout

Stephen Lebans' website

https://lebans.com/

Lebans Table of Contents

https://lebans.com/toc.htm

SaveRelationshipView utility by Stephen Lebans, works in 32-bit Access

SaveRelationshipView

How to make and install an Access add-in on MsAccessGurus

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

Back Story

Stephen Lebans is one of the greatest Access developers of all time. He contributed numerous impressive utilities and controls to the Access community on Lebans.com, which he thoughtfully keeps live but the site hasn't been updated since 2009.

SaveRelationshipView is one of my favorite tools of Stephen's. It only works in 32-bit Access ... until now.

Kent Gorrell, an Australian Access developer, modified Lebans' SaveRelationshipView utility to also run in 64-bit Access, made it an add-in, and changed the form to launch it. Thanks to Geoff Griffith for his valuable insight, me, and Chris for his great feedback and thorough testing.

Geoff's GitHub repository

https://github.com/Access-Abraxas

~ crystal

Share with others

here's the link to copy:

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