Relationship window problem - updates too slow

[Digman]

When I try to move around my relationship window, the screen is constantly
re-drawing all the lines between the tables. It is so slow, I cannot use it.

Is there anything I can do to fix this.

I am using Access 2007. The same database works fine under Access 2003.

 

[Allen Browne]

This usually occurs when the back end is over a network.

Try copying the back end locally (onto your C: drive.) Use the Linked Table
Manager to relink the tables locally if necessary.

 

[Digman]

OK - I think this is a bug.

After painstakingly adding and deleting and linking tables (for days) as you
suggested, I still have the same problem.

More details ...

When I open the relationships view I see my 1 main table linked to 40 other
tables using the same field (ID) in the main table. I can see most of the
tables when window is maximized.

The problem is with the number of links shown in the view (densety). If I
am looking at a view with the one main table with all the links going to it,
every time i move around, the window re-draws the relationship lines
constantly, and it becomes basically un-usable. The relationship lines are
long (don't know if this is relevant). Also, the mouse gets stuck inside the
relationship window, until I click in the window, then I can move it out. I
can't even move the tables in the relationship view (it just keeps re-drawing
lines), in this densely populated area.

If I move away from this table to an area in the relationship view where
there are no other tables, and no relationship lines are shown, everything
works fine.

I am going crazy, or has someone else seen this?

 

[John W. Vinson]

When I open the relationships view I see my 1 main table linked to 40 other
tables using the same field (ID) in the main table. I can see most of the
tables when window is maximized.

It sounds like you have a "sea urchin" data model: do you really have one main
table linked to 40 child tables? Do these tables have child tables of their
own, or relationships between them? What kinds of entities do they represent?

I'm not criticizing here, just curious - it seems unusual!

 

[Digman]

John

No offense taken - I appreciate any response.

I really do have a rats nest of links happening. I do have one main table,
with many links to other tables and those are linked to children of their
own. Many of the tables are used to acheive multiple linking between fields.
For example my one main table contains Engineering Changes (EC1, EC2, EC3,
....). I have another table that lists Standard Tasks to perform. Each EC is
linked to its own custom list of Standard tasks thru an intermediate linking
table. I do a similar thing to link ECs to references, documents,
milestones, resources, POs, WOs, .... Does that make sense?

I am quite certain now that the length of the line used to show a
relationship is the main problem. In other words if you have many linked
tables far away from each other in the relaionship window (easier to read) ,
the connecting lines drawn are long, and this causes a refresh problem. I am
moving all my tables as close as possible to each other, and the response
time is improving (although still quite slow, it is at least usable).

 

[John W. Vinson]

John

No offense taken - I appreciate any response.

I really do have a rats nest of links happening. I do have one main table,
with many links to other tables and those are linked to children of their
own. Many of the tables are used to acheive multiple linking between fields.
For example my one main table contains Engineering Changes (EC1, EC2, EC3,
...). I have another table that lists Standard Tasks to perform. Each EC is
linked to its own custom list of Standard tasks thru an intermediate linking
table. I do a similar thing to link ECs to references, documents,
milestones, resources, POs, WOs, .... Does that make sense?

Yes, but it's *INCORRECTLY DESIGNED*.

You're using a wide-flat, one field per engineering change structure. This is
good for spreadsheets, but it's simply *wrong* for a relational database.

If you have a one (project?) to many (engineering change) relationship, the
correct structure is a one to many relationship - a ProjectID as the Primary
Key of your main table, and a (single!) table of engineering changes with its
own Primary Key, and a ProjectID foreign key. You will probably want an
integer ChangeNumber field as well. The standard tasks can then be linked to
the engineering change table.

The same principle would apply to the other tables: if you have Milestone1,
Milestone2, Milestone3, WO1, WO2, WO3 and so on, you're "committing
spreadsheet" and will have no *end* of trouble (far beyond the quirks of the
relationship window).

Apologies if I've misinterpreted your structure... but if I haven't, you
should stop NOW and normalize your tables!

 

[Digman]

John

I think my design is OK. My relationships with intermediate linking tables
only exist for tables where I am using standard lists. For example I have a
standard list of Tasks (Task1 - Update drawings, Task2 - file drawings, Task3
- hire consultant, ...). If I want to link an EC1 to task1 and task2 and
then link EC2 to task1, task2 and task3, then I use the intermediate table to
link. I use EC_ID as my primary key in EC Table and Task_ID as primary key
in Tasks table. The intermediate table linking them together uses these
fields in a 1 to many relationship. For the above example the intermediate
linking table would have an entry for (EC1, Task1), (EC1, Task2) and then
(EC2, Task1), (EC2, Task2), (EC2, Task3). The linking table would also have
info on % Complete (EC1, Task1 - 100%) and (EC2, Task1 - 50%), resources, ...
It gets very complicated and makes my head spin. Are you suggesting
something different?

 

[John W. Vinson]

If I want to link an EC1 to task1 and task2 and
then link EC2 to task1, task2 and task3, then I use the intermediate table to
link.

My concern is that you (apparently, I may be misreading this) have *FIELDS*
named EC1, EC2, and EC3 in your main table.

This is a "repeating field" and is, it seems, the source of your hedgehog
relationship design.

I would suggest that you have *more tables* (sorry!):

Projects
ProjectID
<project specific information>

EngineeringChanges
ECID <primary key>
ProjectID <link to Projects>
ECNumber (1 for EC1, 2 for EC2, 47 for EC47 if you have that many)
<information about this EC as a thing in its own right>

Tasks
TaskID
TaskDescription

ECTasks
ECID <link to EngineeringChanges>
TaskID <link to Tasks>
<information about THIS task with regard to THIS EC, e.g. DateAssigned,
DateCompleted, success or failure>

This gives you four tables with three relationships, instead of scores of
relationships, and instantiates the same logical relationships; it's actually
*better* since you have only one field to search for EC information rather
than three (or multiple).