Frontend table relationships different than backend. Why?

[mafhobb]

Hello.

I created a database a few years ago and it has been working well. This database is split.

Recently I started to update the front end based on user feedback and I am adding some queries.

I am noticing that sometimes I try to create a new query and Access tells me that the tables that are involved in the query are not related. Sure enough if I check in the front end, the table relationships are not the same as those in the back end and while the table relationships I need exist in the back end, they do not in the front end.

While the relationships were initially created in the unsplit database, it is possible that I added some of them later during past development/updating processes when the database was already split. This is the only reason I can think of for them to be different.

When I add a relationship in the back end (new table or bug fix) do I also need to add it to the front end if the database is split? if not, why would they be different? What kind of issues can this create?

Thank you

mafhobb

 

[namliam]

When I add a relationship in the back end (new table or bug fix) do I also need to add it to the front end if the database is split?

Yes, seperate databases, seperate relationships

Only reason to "Update" the relationships is
1) for query building
2) referential integrity
3) self documenting of the database

 

[RuralGuy]

As you have already discovered, the relationships do not sync between FE and BE. Your relationships should be in the FE where you create Queries.

 

[mafhobb]

Ok. So...

I am leaving the BE as is, with all its table relationships and I am modifying the FE table relationships, attempting to duplicate what I have in the BE.

However, when I go to duplicate the very first table relationship in the BE to the FE, which is a one-to-many relationship, I simply get a black line between the fields in the two tables. No symbols of one-to-many on the line, although in the "Edit Relationships" pop up it does state "one-to-many" as a relationship type. Also, I cannot select any of the three options in the pop up "Enforce referential...., cascade..., cascade..."

What now?

mafhobb

 

[sumox]

BE TABLE RELATIONSHIPS ARE ONLY FOR TABLE TO TABLE

BUT FE TABLE RELATIONSHIPS ARE FE TO FE QUERIES/TABLE/QUERIES

and if there's something reg. update to Frontend from backend
then goto Tools-Database Utilities-Linked Table Manager

Click Select all
Click Ok
Now all tables and relationships will be updated/refreshed/re-queried from Backend server

Note:- FE is dumb about updating Tables/ and basic Relations from BE
You must set some VBA/Macro to update or manually update
or make skedule for each PC User to update once a week or as per designed

 

[mafhobb]

Hi Sumox.

So...as long as the table relationships between the tables in the backend are defined in the backend then I do not need to worry about the tables and their data, correct?

Only when creating a query in the FE in design view I need to worry about the relationship between the tables in the query (which is generally indicated with a thin, black line). Correct?

mafhobb

 

[sumox]

note, understand clearly dear mafhobb :
table relationships in backend must be updated to frontend version in each pc
second thing in frontend
IF you missed to create any relations between tables/queries in B.E
then it can be created in frontend which does save on frontend
So if there are 5 PCs with F.E and 1 Server/PC with B.E
you create Query new , on 1st PC
it will not update on other 4 PCs with F.E
To Update, fastly :
Just copy paste Newer Version on balance 4 PCS
and on each PC click - Tools-Databas Utility-Link Tab Manager- Click Select ALL
Click always prompt Click Ok
now dialog box appears asking you location where Backend is saved . locate --
"all .... were refereshed" dialog appears means, all things good

Second slower method
Click File-Import Ext. Data- select newer version file on network
now list of tabs will display. now select Query tab, and select which query to update/download to current FE Version

Click ok.... All done !

 

[Galaxiom]

The only relationships that really should matter are on the real tables in the back end. However even they don't really matter if referential integrity is not enforced.

The front end relationships can be an aid to designing because they automatically create the joins in queries.