Lose relationships when re-linking FE and BE

[bewir]

I have a database split into front and back ends. All the tables remain in the back end. On a quarterly basis I have to collect the back ends of each of the sites entering data. (I know it would have been easier to use replication but the non-profit I'm helping out could not afford developers edition). When I re-link any one of the back ends to my front end, all the relationships are gone so the queries that they were based on are useless. I set up all the relationships with the tables before I split the DB, and when I designed the queries, as expected they "took on" or assumed the correct relationships automatically. Now when I design new queries they have no relationships linking the tables whatsoever. Is this a bug or am I doing something terribly wrong?
Thanks

 

[Steve101]

You should always set up your relationships on the backend database, where the data actually exists.

This way, the relationships and their characteristics, specifically referencial integrity will automatically be inherited by the client programs.

On the client program, in the relationship window, you will only see tables and relationships for clients which have been linked to the backend tables. You may have to press the "Relationships, ShowAll" menu options to expose these.

Hope this helps,
Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au

 

[VBslammer]

There's nothing more to add, Steve is exactly right...

 

[bewir]

Thanks for the reply. I have obviously not posted my issue clearly or I'm still not understanding. My relationships are indeed set up in the back end tables. If I enter the back end DB directly and look at the relationships, they are still quite intact. But those same relationships which carried over to the front end when I was designing the queries, are no longer to be found. For example I have two tables in the back end "StudentStaticData" and "StudentPerformanceData". They are linked by a primary key in the former table (StudentID) and its corresponding foreign key in the latter table. When I was initially designing the query in the design mode for the front end, as I added each table to the field lists window, they would automatically show the tables to be related. Exactly as they were when I originally set up the tables and relationships before splitting the database. Since I have moved both front and back end to a different computer and re-linked them using the linked table manager, I can go into the same queries in the design mode and the relationships between the tables are no longer there. So while the relationships appeared to be inherited just fine before I moved the database, that same inheritance is gone from my front end now.
Put another way, if I can go into my back end DB and open the relationship window and see all the intact table relationships, should I then not be able to go into the front end (which is properly linked to that back end) and open the relationships window and see the inherited relationships between tables? If so, they are not showing up. Thanks again for the help in advance.
Brian

 

[Steve101]

Brian,

I believe this is a common problem. I have certainly experienced it myself. I think that selection of the Relationships, ShowAll options should recover them into your client.

However, the important thing is that the relationships are established in the backend. This assures you that the referencial integrity, if set up, will always be applied.

Steve Cheers,

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au