Relationship Diagram - Access crashes when tables are added to it

[Steven Kogan]

I have a problem with Access 2007, SP2, on a particular PC.

For a particular database, when using the Relationships view Access crashes sometimes when a table is added. It always crashes when I display all relationships.

Which table I add to the relationships view doesn't matter, so it seems to be related to the number of tables on the view.

In total there are only about 20 tables in the database.

It seems to be some sort of database corruption, but I can't seem to fix it.

Has anyone else encountered this problem? Has anyone found a solution to this glitch?

[RuralGuy]

What OS? Is the OS fully patched? Import the db onto a fresh db and see if the problem follows. Have you tried Compact and Repair?

[Steven Kogan]

Windows Vista, with all updates.

I've tried importing to a fresh db, and tried Compact and Repair.

[RuralGuy]

There are some issues that are unique to Vista. I don't know if this is one of them. Allen Browne usually has a pretty good list. Maybe he has noted it. http://allenbrowne.com/Access2007.html

[Steven Kogan]

I checked the site and haven't found this particular problem mentioned.

Has anyone encountered the problem where Access crashes when a table is added to the Relationship Diagram?

[Scotty Davis]

Yeah, it's INFURIATING. Can't make any joins, can't edit any joins made in previous versions, CAN'T CAN'T CAN'T.

[Steven Kogan]

In my case the database relationships worked on a different computer.

I still haven't managed to fix the problem on the original computer though.

[Micho2008]

Yes, this problem happens with me! I am really fed up with Ms Access 2007 and thinking to move to FoxPro.

[Micho2008]

It seems I found a trick to solve the problem! Do the following easy steps:

1-In the database where the problem occurs (say DB1), delete all the relationships.

2-Create a new database (say DB2).
3-In DB2, import all the tables from DB1. (Do NOT import anything else: Queries, forms, ...)
4-In DB2, recreate the relationships.
5-In DB2, create the other objects (queries, forms, etc.). You have to create the forms and modules from scratch. However, you don't need to rewrite the code in the modules; you can just copy it from DB1.

I hope this works for you.

[Micho2008]

At first it seemed to me that the problem is because Access cannot handle some automatic options. However, after few days of trying to solve the problem I've found it is easy but annoying. The problem is simply because the Relationship window cannot display relationships that exceed a certain number. The solution is simple:

In the Relationships window, once you finish with setting all the relations of a certain table, simply hide that table (right-click on it and choose "Hide Table"). This keeps the number of related tables that are displayed at minimum and solves the problem.

If later on you want to check the relationships related to a certain hidden table, do NOT choose Show All. Rather, hide some related tables, and then choose Show Table, and then double click the table you want.

If your Relationships window is already corrupted because it has many related tables, do the steps I mentioned in the prior post. (As you build the relationships, do not forget to hide the table that you've finished with.)

NOTES:
1-This problem does not mean that the relationships are not built or that Access cannot handle them. Rather, the problem is only with displaying the relationships.

2-After you've built the relationships, if you want to just check them, you can use the following way:
In the Database Tools tab, choose Database Documenter. Then choose all the tables that you want to check their relations. Then, click the Options button and enable only Relationships. Then click Ok. Access will create a report of all the relations.

[Steven Kogan]

Micho2008, have you tried doing a re-install of MS Access, or trying your database on a different PC?

The problem only occurs on a few computers or installs.

Chances are if you try the same database on a different computer you will be able to view all relationships, however if you put the database back on a problem PC it will crash when opening a relationships window with too many relationships on it. In that case, reduce the number of relationships on a PC that doesn't crash at the database window...

I'll get around to doing a re-install and see if that fixes the problem on my PC.

[karrenb]

I found the solution. I had this problems as well. I got a new laptop with new operating system, new Office installation and it happened again. I couldn't believe it, but then I realized the only link between the two was my Quick Access toolbar. I had brought the access.qat over from the previous machine. When I deleted the access.qat my relationships worked fine.

I haven't tried to rebuild the .qat to find the culprit, but I will rebuild that very slowly checking out the function of relationships along the way.

[RuralGuy]

Thank you very much for that update!

[Steven Kogan]

Deleting access.qat fixed the problem for me. Thanks!

[twinnyfo]

What a crazy solution! But Access does seem to have a mind of its own...