Disappearing relationships

[Dan]

In 10 years with Access, I've never run into this before.

The relationships in my database disappear! If I create a
relationship between 2 tables on the Relationships page,
then save any changes and reopen Relationships, the
relationship I just created does not show up.

I've tried:
(1) Repair and Compact -- No effect.
(2) Creating a new database and importing all tables and
objects. This is only partially effective - some of the
relationships created initially survive, some do not.
Further attempts to save changes in Relationships fail.

Clues:
(1) In the MSysRelationships table, the successful
relationships have some sort of coded response in the
szRelationship field (Example: {01F3826D-8EB7-4282-9D58-
7F98983DE579} ) and the unsuccessful ones have a
concatenation of the two table names (ie Address +
Rebates = AddressRebates)
(2) When the Relationships page is opened, some tables
are put in 2 or 3 times (ie Address and Address_1). [I
think this is normal, but I mention it in case it helps.]
(3) Backstory: This database has 41 tables, many of which
are linked to each other in multiple interconnections.
There is duplication of some fields due to a business
rule of accounting double-entry.

Has anyone experienced something similar? Any suggestions
on how to resolve this?

 

[TC]

Sure you're clicking the "Show All" button (or whatever it's called)?
Sometimes the relationships will not all show, unless you do that. I've
never bothered to understand what Access means by "some" relationships
(versus "all" relationships).

HTH,
TC

 

[Allen Browne]

Disappearing relations indicate a kind of corruption. This was fairly common
back in Access 95, and seems to have resurfaced in the versions that support
Name AutoCorrect. What version of Access are you using? And what service
pack?

To rebuild your corrupted database:
1. Go to http://support.microsoft.com, the Downloads section, and get the
latest service pack for your version of Access and also for JET 4. (The JET
4 one is also crucial.)

2. After applying the service packs, create a new (blank) database:
File | New

3. Immediately uncheck the Name AutoCorrect boxes under:
Tools | Options |General

4. Import the tables *without* the corrupted relationship:
File | Get Extenal
Use the Options button in the Import Objects dialog to not import the
relations.

5. Rebuild the relations:
Tools | Relationships

You should now have a stable database where the relations do not disappear.