Relationship window in backend or frontend

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/
__________________________________________

"Sylvain Lafontaine" wrote:

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)
__________________________________________

"Lars Brownies" <Lars.DeleteThis@Brownies.com> wrote in message
news:hc1vsl$20q9$1@textnews.wanadoo.nl...

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

Tom Wickerath <AOS168b AT comcast DOT net> wrote:

>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/

On Sun, 25 Oct 2009 17:54:46 +0100, "Lars Brownies" <Lars.DeleteThis@Brownies.com> wrote:

>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.
--

John W. Vinson [MVP]

Thanks all for Most Valuable info!

"John W. Vinson" <jvinson.DeleteThis@STOP_SPAM.WysardOfInfo.com> schreef in bericht
news:lba9e5pb7fl0c4tuba8m9ucquj4luv1smc@4ax.com...
> On Sun, 25 Oct 2009 17:54:46 +0100, "Lars Brownies" <Lars.DeleteThis@Brownies.com>
> wrote:
>
>>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.
> --
>
> John W. Vinson [MVP]