Lost relationships between tables

Right click over a blank area of the Relationships Window and click Save Layout.

" I can add the join lines and check R. Integrity and save the changes."

When you open and see no lines ... if you Right Click over a blank area and select Show All ... do you see them then?

if not ... do a Compact & Repair on the db.

There all there...as Joe said, do  "show all".

It's important to understand that the layout and the relationships themselves are two separate things.

Also that the tables don't represent the relationships, but rather the lines between the tables.   If you click on a table and delete it, it does not delete the relationships.

To delete a relationship, you must click on the line between tables, then delete.

Jim.

I did a compact & repair of the db and retried all your suggestions with no different results.  Also, I am now getting an error message saying that my main table has too many indexes.  Currently there are 12 in that table.

<<I did a compact & repair of the db and retried all your suggestions with no different results. >>

"Show all"  should show everything, although the tables may be off the screen.  That can happen if you have multiple monitors and then work with one.

<< Also, I am now getting an error message saying that my main table has too many indexes.  Currently there are 12 in that table. >>

 The index limit is 32, and when you create a relationship to another table and enforce RI, ACE (the database engine) will create a hidden index on the many side of the join.

 If you can, post a copy of your DB with just the tables in it if you still can't find the relationships.

Jim.

BTW if:

"Show all"  should show everything, although the tables may be off the screen.  That can happen if you have multiple monitors and then work with one.

 did happen, or you still can't see things, do this:

1. Open the relationship window (make sure it was closed first).

2. Hit the delete key about 50 or 60 times, enough that every table you might have in the layout is deleted.

3. Right click, then "save layout"

4. Close the window

5. Open the window backup and do "Show all".

Everything will be reset into the upper left.

Also, don't forget; the relationship window does have scroll bars! <g>

Jim.

Sounding like something IS hosed up here.
Open the MSysRelationships table and see if there are entries for the Relationships you are expecting.

mx

I tried deleting all tables, saving layout, closing window and then reopening.

Regarding the MSysRelationships table, where is it?  I'm running Access 2003 does that make a difference?

Sorry, I missed mentioning that going thru the delete tables process, did delete all the tables.  I have one main table and 11 subtables I'm trying to link to it.  So it's not very complex.

Can you upload the db, removing any sensitive data ?

MSysRelationships ... probably hidden.  In Options ... you have to Check Show System tables and Show Hidden Objects (not exactly sure of the nomenclature - it's been awhile since 2003)

btw ... just checking ... these are *not* linked tables are they ?
If so ... you would need to be in the actual back end to see the real relationships ...

mx

Yes, they are linked tables and I am in the backend.

OK, I found the MSysRelationships table.  What am I looking at?  (I'm reluctant to send tables, since them contain proprietary information.)

"I have one main table and 11 subtables I'm trying to link to it"

Link ???
See my last post above about Linked Tables and Relationships ...

m

Well, you should see all of the relationships ... more or less and entry for each Relationship, Table and Field names, etc.

"(I'm reluctant to send tables, since them contain proprietary information.)"
For this issue, you could delete all the data in the tables and upload an empty db ... should be sufficient.

I will be gone for a couple of hours.  Jim s/b able to help in the meantime ...

mx

I just went into a saved backend from several years ago and looked at its MSysRelationships table.  The older one has only 12 entries.  The current one has 41 entries with lots of duplicates.  

Is this the problem.  Can I cleanup this table using the older one as a reference?  What is this table used for anyway?

You cannot edit or delete the MSysRelations table, as it is a (Access) system table. The purpose is of course ... to hold all of your defined Relationships. Not sure why there would be dups ... seems odd, although not a table I look at very often.

If there is any way you can upload a copy of the db minus data, that would be helpful. Something quite odd is is going on here.

mx

Too bad.  I'll get an empty db to you but in won't be until next Wednesday, I work part time.  

Thanks so-o-o much for your help.  I'm really out of my depth here.

Judith

We are VERY curious to see what is up with this ...
Until then ....

OK, I just make a copy of my db, copied the structure of the appropriate tables, deleted the full tables and renamed the empty tables to the original names.  I had to recreate the relationship screen.

Then I looked at the MSysRelationships table and everything looks fine!

I created a blank db, imported all the tables and recreated the relationship screen.  The duplicates are back!!

I'm going to try again, not by copying structure, but by deleting all records with a ContactID greated that 10.  ContactID is the field all the relationships are based on.

J

OK, I think that this will work,  see attached db.

J
Cdata27a0317B.mdb

OK I have it ... checking out now

So ... at the moment, I see all the Relationships ....
?

I'm assuming you mean for me to check out tblContactNotes.  If you right click on the relationship line, you'll see the relationship is ContactID to ContactID.

J

Well ... this was the original  problem:

"When I look at the relationships screen, all join lines are missing. "

So ... in the db you uploaded, all (or most) of the Join Lines are there ... so, not sure what the issue still is ?

However ... not sure why there are so many tblContact_1, 2, 3 .... and so on tables ?
Also for example ... tblContact and ...  tblContact_1 (and 2,3 ..... 7) all go to tblContactType, which doesn't make any sense.  
Is this what you are saying is the problem ?

And tblContactType has ContactID as part of a Compound Primary Key ... which doesn't make any sense either.  This table should have it's own ContactTypeID - Auto Counter as a primary key ... and should be a Foreign Key in the Contact table ... and so on.

Overall it's very hard to follow ...

Are you saying that when I discovered that the join lines were missing and I tried to reestablish them, I was actually creating a duplicate set without knowing it?  That is a real possibility.

However, the question now is how do I correct the MSysRelationships table which resulted from my many tries to correct relationships(I know I need to go through and correct the Primary Keys and Indices in these tables before anything else).

Once I do that, how can I correct the MSysRelationships table.  Would it help if I export it to excel and upload it for you?

Judith

I decided to upload the exported MSysRelationships tables for you.  The one without dupes is from our db for several years ago.  The one with dupes is the one we are running with today.
MSysRelationships.xls

I tried the same thing.  I went true the tables first and corrected the Primary Keys and Indexes first.


Then I did what you did.  Everything looked fine, but to be sure I click Show All.  There were several tables that popped up and I deleted them and resaved.  MSysRelationships still had 42 entries.  When I went back to the Relationships view, they were all back!

J

It's fixed, thanks to you.  I created a new, blank db and imported the tables one at a time.  Before I showed them in the relationships view, I corrected the Primary Keys and indexes.  Each time I checked the MSysRelationships table.  And it worked!

I just wish I could give you a 1000 points or more for hanging in there with me and being so patient.

I am so-o-o grateful to have this puzzle solved and I learned so much.  

Thanks again!

Judith

The database I uploaded does not have this issue ... did you test with that one ?

mx

No problem.

I assume you can going to fix the 'foreign key' issue ... etc ?

Where are you located btw ?

Yes, after looking at the keys and indexes, I realized in how bad shape they were; some had p-keys they didn't need.  I fixed them all before I started.

I'm located in Sedona, AZ!  And work for a non-profit Verde Valley Caregivers, who provide services for homebound seniors.

We are going to be migrating to Access 2013 soon.  Any words of wisdom?

J

Current version is A2003 right ?

Hey ... Sedona, the land of the UFOs.
Unsealed: Alien Files (et al) one of my favorite shows :-)

mx

Yes, currently using A2003.

 Yes, I've lived in Sedona since '97 and haven't seen a UFO yet :-(

My favorite is Ancient Aliens.  They are watching us, wonder when we'll admit it?!?

I have the Ancient Aliens DVD set ... awesome.
"We' ... as in the Government of course.  The evidence is massive and overwhelming and irrefutable.

A2003>A2013  .... aka Ten Years Later :-)
Well ... there will be some shell shock of course. Most notably will be the Ribbon paradigm which has replaced the (now) legacy menu bar system.  The  'database window' (aka database container, et al) has been replaced with the Navigation Pane (or Pain as some feel). I find the Ribbon and Nav Pane superior to anything previously offered, although it was not an instant love affair. However, many developers do not share this sentiment on either. But they are here and not going away.

The entire development UI is cosmetically different, and many nice new features.  And controversial Access Web Apps were introduced in A2013, replacing the basically useless Access Web Database paradigm in A2010. But this is an entirely different subject - from the Desktop side.

Ping back when you are about to or have migrated ...