lost table relationships

[Guest]

Hi,

I am using Access 2003, front end tables are linked to the back end, links
are checked every time the db starts.

In my front end, I had 100 % of the relationships gone in 100 % of my
queries. Checking the backend, all relationships were present and intact.

Has anyone seen this "feature" before, and is there a safeguard against it
(other than putting all queries into a module and calling subs from a
switchboard form)?

Thanks a lot in advance for the assistance.

 

[Jason Lepack]

The relationships aren't required in the front end. They're only
required in the back end to enforce referential integrity. Joins in
queries don't require relationships, just fields with common
datatypes.

Cheers,
Jason Lepack

 

[Guest]

Jason, thanks for your quick response. Sorry if I made my problem not clear
(must have got "lost in translation", I'm using the German language version
and may not have caught the correct English wording).

What I was referring to are the lines indicating joins. In the design views
of my queries, not a single line was present, meaning there were no joins at
all. I didn't check the relationships window in the FE, only in the BE -- and
all the joins were present.

Meanwhile I repaired all queries. I still wonder how this came about and how
to avoid it. Thank you very much.

 

[Michel Walsh]

That is by design. You cannot enforce relationship between tables in
different database. Relationships can only be handled in the NATIVE
database. In your case, that is in the backend, assuming your front-end has
only LINK to the back-end data. Since your front-end does not hold the data,
it cannot enforce anything on it. It is enough, and safe, that your back-end
does it. You don't have to try, in the front-end, to 'double' the job being
already done by the back-end database.






Vanderghast, Access MVP

 

[Guest]

Michael, thanks for your message.

I wasn't talking about the relations shown in the Relations window (neither
front nor back end). They existed in the back end, and I didn't try to
"rebuild" or "double up" any of them in the front end. It has been my
understanding (experience backs it) that linked tables in a front end inherit
their join relations from the back end.

What happened was that all of the join relations had disappeared from the
design views of all of my queries in the front end. Consequently, queries
produced results of umpteen thousand records when half a dozen would have
made sense.

One may inadvertantly remove joins from one or very few queries, but 100% of
joins from 100% of queries? And not remember? After all, it took quite some
time to re-establish them (in query design, not Relations window) and
deliberately removing them would have taken some time, too.

Hence my question: Has this "added feature" (never happened with A97 nor
A2000) been observed before and if so, is there a safeguard against or
workaround to avoid it -- preferably short of creating qdf.execute subs in
VBA.

Thanks again.