Cant view relationships of a split db

[nosferatu26]

Hello,

I have been working on a database for quite some time now. A few months back I decided to split the database and now im running into an issue. I have about 30+ tables in the back end and I now learned that I have to add more. When I open the back end file, I see all of the tables there, but when I try to view all of the relationships they don't show up for some reason. I suspect its because I set these tables, as well as their relationships, up before the database was split. Is there anyway to be able to see the relationship layout like before?? I think if I add new tables to the back end file now they do show up; but being able to see the rest of the tables would help tremdously.


Any insight is much appreciated !

 

[sneuberg]

Maybe they are just hidden. Try clicking All Relationships in the ribbon or right click in the Relationships window and click Show All

 

[nosferatu26]

I tried that and only 5 of the 30+ tables appear. Evidently those are the ones I have added from inside the back end file. I am not sure why I cannot view all of the original relationships.

 

[sneuberg]

I suggest looking at the MSysRelationships table. It's hidden so you will need to change the Navigation Options to Show Hidden Object and Show System Objects. You can get to the Navigation Option in the Access Options, Current Database or right click on the top of the Navigation Pane where it shows whats being displayed. There should be a record in this table for each relationships. If they are not in this table they must have got lost during the split operation. If this is the case do you still have a copy of the system before it was split?

 

[The_Doc_Man]

Generally, the relationships have to follow the tables, but it is possible to define relationships to linked tables (I think) so check the FE to see if they were dropped there.

 

[nosferatu26]

Ive found that for some reason in the FE, when I am in the design view for a query for example, the relationships do appear. However when I actually go to the Design tab and select relationships, all of the tables appear but their relationships have vanished also. I think they still exist because the lookup for individual fields that link to other tables remained in tact, but for some reason I had to manually re-link the tables in that view. Being able to see all of the tables with the lines that connect to them is very useful I don't understand why splitting the db removed them from view.

 

[jdraw]

This is a quote from Boyd Trimmell (HiTechCoach on many forums)--from another forum.

The main reason I define relationships is to set the option to Enforce Referential Integrity. I do believe it is best practice to do this. This can only be done in the database with the tables. In a split application this is done in the back end. It does not matter if the back end is JET, ACE, MS SQL, etc. So if best practice is to use Referential integrity at the database engine level then it would also mean that it is best practice to create the relationships in the back end.

If you follow the advice of only defining the relationships in the front end, then you will not be able to have the database engine Enforcing Referential Integrity. This means you will have to do a lot of extra VBA coding to Enforce the Referential Integrity yourself in the front end.

Additional info from a post by Ken Sheridan

If you create relationships in the front end the only thing it achieves is that it determines the default joins types when you create a query in design view. To ensure data integrity through enforced relationships they must be created in the back end. ..... You can if you wish create them in the front end in addition to, but certainly not in place of, those in the back end.

Good luck.