Lost relationships between tables

When I look at the relationships screen, all join lines are missing.  I can add the join lines and check R. Integrity and save the changes.  However, after I close and then go back in the lines have disappeared.

When I print out the relationships they are all there!

Not sure if the relationships are there or not!

What can I do.

Judith
JudithARyanTech SupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Right click over a blank area of the Relationships Window and click Save Layout.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Relationships
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
" 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.
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
JudithARyanTech SupportAuthor Commented:
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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sounding like something IS hosed up here.
Open the MSysRelationships table and see if there are entries for the Relationships you are expecting.

mx
0
JudithARyanTech SupportAuthor Commented:
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?
0
JudithARyanTech SupportAuthor Commented:
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.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
JudithARyanTech SupportAuthor Commented:
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.)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
"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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
JudithARyanTech SupportAuthor Commented:
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?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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
0
JudithARyanTech SupportAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
We are VERY curious to see what is up with this ...
Until then ....
0
JudithARyanTech SupportAuthor Commented:
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!!
0
JudithARyanTech SupportAuthor Commented:
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
0
JudithARyanTech SupportAuthor Commented:
OK, I think that this will work,  see attached db.

J
Cdata27a0317B.mdb
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK I have it ... checking out now
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So ... at the moment, I see all the Relationships ....
?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
R
0
JudithARyanTech SupportAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ...
0
JudithARyanTech SupportAuthor Commented:
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
0
JudithARyanTech SupportAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
OK ... I deleted all the extra tblContact_N tables and Relationships directly from the Relationships view.
MSysRelationships looks normal to me now ... 22 entries instead of 45.

Still some things to fix ... for example, in tblContactLinks, ContactID is a 'Double' ... must be a Long Integer in order to be a Foreign Key. A couple of other tables have similar issues.
Cdata27a0317B-MX01.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JudithARyanTech SupportAuthor Commented:
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
0
JudithARyanTech SupportAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
The database I uploaded does not have this issue ... did you test with that one ?

mx
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
No problem.

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

Where are you located btw ?
0
JudithARyanTech SupportAuthor Commented:
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
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Current version is A2003 right ?

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

mx
0
JudithARyanTech SupportAuthor Commented:
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?!?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
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 ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.