Relationship window in backend or frontend

[Lars Brownies]

What is the recommended way to maintain the table relationships in the
relationship window? Should I only work in the backend or are there reasons
to also maintain them in the frondend? I noticed that changes in the backend
don't automatically show in the frontend.
Thanks,
Lars

 

[Sylvain Lafontaine]

The FE and the BE are two separate things and are not related at all in any
way when it comes to relationshiups and their effect (like cascading delete
or enforcing primary key/foreign key fields). The only purpose to set
relationships on the FE is for documentation purposes only and has
absolutely no effect on the backend and will not be verified, tested or used
or have any other effect whatsoever when Access/JET is working (making
queries) against the BE.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Tom Wickerath]

Hi Lars,

In addition to Sylvain's reply, I wanted to comment on this statement:

I noticed that changes in the backend don't automatically show in the frontend.

In the front-end application file, delete the linked tables that are
involved in a relationship that you've edited in the back-end database. Do a
Compact and repair of the FE application file. Then re-establish the linked
tables. You should now see an updated representation of the relationships
diagram in the FE. My advice is to go through this process anytime you make a
design change to a table in the back-end database (ie. delete the linked
tables involved, compact, and re-establish from scratch). Access caches a lot
of information about the structure of the linked table. This cached
information can be inaccurate if you make design changes without refreshing
the link. This process, in my opinion, is better than simply using the VBA
RefreshLinks method.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

:

The FE and the BE are two separate things and are not related at all in any
way when it comes to relationshiups and their effect (like cascading delete
or enforcing primary key/foreign key fields). The only purpose to set
relationships on the FE is for documentation purposes only and has
absolutely no effect on the backend and will not be verified, tested or used
or have any other effect whatsoever when Access/JET is working (making
queries) against the BE.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)
__________________________________________


What is the recommended way to maintain the table relationships in the
relationship window? Should I only work in the backend or are there reasons
to also maintain them in the frondend? I noticed that changes in the backend
don't automatically show in the frontend.
Thanks,
Lars

 

[Tony Toews [MVP]]

In the front-end application file, delete the linked tables that are
involved in a relationship that you've edited in the back-end database. Do a
Compact and repair of the FE application file. Then re-establish the linked
tables.

I agree with the relinking of the tables when you change the
definition of a table in the BE. But I personally haven't ever seen
the case where you need to do a compact of the FE.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a free, convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

 

[John W. Vinson]

What is the recommended way to maintain the table relationships in the
relationship window? Should I only work in the backend or are there reasons
to also maintain them in the frondend? I noticed that changes in the backend
don't automatically show in the frontend.
Thanks,
Lars

Just to add to what Sylvain and Tom have said: the main purposes of
relationships are 1) to maintain referential integrity and 2) to create
indexes in the tables which maintain that integrity and also improve
performance.

Those functions can occur ONLY in the database containing the tables - the
backend. Relationships created in the frontend are "advisory" only; they
cannot prevent "orphan" records from being created, and they cannot affect the
indexing of tables in the backend. The only real benefits they have are to
provide a "hint" for Access to create appropriate joins when you create a
query, and for documentation.

I'll add an emphatic YES!! to Tom's suggestion: if you change any
relationships (or field types, or field sizes, or indexes, or added or deleted
fields) in a backend table, open the frontend; delete ALL the tables (table
links that is to say), and create new links.

 

[Lars Brownies]

Thanks all for Most Valuable info!

Just to add to what Sylvain and Tom have said: the main purposes of
relationships are 1) to maintain referential integrity and 2) to create
indexes in the tables which maintain that integrity and also improve
performance.

Those functions can occur ONLY in the database containing the tables - the
backend. Relationships created in the frontend are "advisory" only; they
cannot prevent "orphan" records from being created, and they cannot affect
the
indexing of tables in the backend. The only real benefits they have are to
provide a "hint" for Access to create appropriate joins when you create a
query, and for documentation.

I'll add an emphatic YES!! to Tom's suggestion: if you change any
relationships (or field types, or field sizes, or indexes, or added or
deleted
fields) in a backend table, open the frontend; delete ALL the tables
(table
links that is to say), and create new links.