freezing the Relationship diagram

[Christopher Glaeser]

When I close/open the relationship diagram, Access redraws all the tables
and links. The Access version has orphaned tables, criss-crossed links,
etc. My drawing is much easier to read. Is it possible to freeze the
Relationship diagram so Access won't redraw my version?

Best,
Christopher

 

[M.L. Sco Scofield]

After you rearrange the relationship window, close it and click Yes when it
asks you to save.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Christopher Glaeser]

After you rearrange the relationship window, close it and click Yes when

it
asks you to save.

Thanks for the quick response. I always save the changes, but Access often
redraws the links. Some of my tables are linked two, three, and four times
or more. So, I have Table, Table_1, Table_2, Table_3, etc. I place all the
tables nice and neat, with no crossovers. After saving, closing, and then
opening the Relationship diagram, Access has turned my neat drawing into
unreadable spaghetti. Table_1, Table_2, and Table_3 are now orphaned, and
all the links to Table crisscross all over the place, crossing other tables
and other links. It's a complete mess!!! Am I doing something wrong? Is
it possible to fix this?

Best,
Christopher

 

[M.L. Sco Scofield]

Christopher,

No idea. I personally have never heard of this problem.

What version of Access are you using? (You never mention that.)

Do you have all of the service packs installed for your version? Take a look
at http://support.microsoft.com/kb/303528/EN-US/.

There is a possibility that your database has one of those strange,
un-repairable corruptions in it.

Try importing all of your objects into a brand new mdb file. (This is
guaranteed to scramble the relationship window.) Try rearranging a few
things and see if they stay rearranged.

Good luck.

Sco

M.L. "Sco" Scofield, Microsoft Access MVP, MCSD, MCP, MSS, A+
Denver Area Access Users Group Vice President www.DAAUG.org
MS Colorado Events Administrator www.MSColoradoEvents.com
Useful Metric Conversion #18 of 19: 8 nickels = 2 paradigms (My personal
favorite)
Miscellaneous Access and VB "stuff" at www.ScoBiz.com

 

[Christopher Glaeser]

No idea. I personally have never heard of this problem.

Interesting.

What version of Access are you using? (You never mention that.)
2003.

Do you have all of the service packs installed for your version? Take a
look
at http://support.microsoft.com/kb/303528/EN-US/.

Not sure. I'll check.

 

[david epsom dot com dot au]

It's a problem with showing the same table multiple times
in the diagram. Access can't handle diagrams like that
very well -- all versions.

(david)

 

[Christopher Glaeser]

It's a problem with showing the same table multiple times

in the diagram. Access can't handle diagrams like that
very well -- all versions.

Thanks, it's good to know it's not just me or something I did wrong.

Access seems to handle multiple instances of tables in Queries (at least all
of them seem to be working thus far), but really screws up the Relationship
table. I have about 15 tables, and about five of them are linked multiple
times. In addition, there may be more than one link between the same two
tables. For example, tblMessages is linked twice to tblEmployees, once for
TakenBy, and once for AssignedTo. Access has made a complete mess of my
Relationship diagram. It's almost to the point of unreadable. If Access
would just leave my tables and links alone, it would be very easy to follow
the logic.

Best,
Christopher

 

[david epsom dot com dot au]

Remember that the automatic join procedure when creating
queries in design view uses the same list of FK indexes
that the relationship view uses: if the relationship
window gets confused about how to draw relationships,
the query design helper will suffer from the same confusion.

The result is that the SQL may include joins that are not
immediately apparent from the query design view window.
Unexplained non-updatable queries are a common result.

(david)

 

[Christopher Glaeser]

Remember that the automatic join procedure when creating

queries in design view uses the same list of FK indexes
that the relationship view uses: if the relationship
window gets confused about how to draw relationships,
the query design helper will suffer from the same confusion.

Yep, I finally got one last night when designing a new query.

The result is that the SQL may include joins that are not
immediately apparent from the query design view window.
Unexplained non-updatable queries are a common result.

What would you recommend is such situations?

Best,
Christopher

 

[Christopher Glaeser]

I thought perhaps I had corrupted my database, so I started from scratch by
creating a new database and importing only the tables (no relationships). I
opened the Relations window, created the links, closed the Relations window,
then opened the Relations window. Tables with multiple links like
tblStatus_1 and tblStatus_2 were orphaned, and the links were redrawn to
tblStatus. My beautiful diagram was converted to speghetti. As best I can
tell, this is a "feature" of Access. How do Access developers view the
relations of a complex database?

Best,
Christopher

 

[Immanuel Sibero]

Hi Christopher,

Are you using lookup function on the table definition?

http://www.mvps.org/access/lookupfields.htm

You should implement lookup function on forms.


Immanuel Sibero

 

[david epsom dot com dot au]

What would you recommend is such situations?

This information came too late for me! I still don't know
if I had problems with the SQL or if the query really was
non-updatable for some reason. But I guess what I would
recommend is turning off the auto-join feature for query
design, and doing all your own joins when you add a table
to a query.

(david)

 

[Christopher Glaeser]

Are you using lookup function on the table definition?

http://www.mvps.org/access/lookupfields.htm

You should implement lookup function on forms.

Thanks for asking, but no, I have not used any lookup functions in any of
the tables.

Best,
Christopher