Front end/Back end table relationships

Hello.

I'm hoping someone can help me with a problem that just started
happening today.

I have a database tool that's split into a front end and back end. The
back end has various tables, and a bunch of relationships set up
amongst those tables. The front end has all of these tables linked.

Normally, when I create a query that contains any of those related
tables, Access automatically adds the appropriate joins in the query.
This was happening fine up until today. Now, it doesn't add any of the
joins, even though the relationships are set up fine in the back end.
I haven't made any changes in either the front or back end, so I don't
know why it has suddenly stopped working.

I've tried various things, such as relinking all the tables,
compacting and repairing both database files, etc. Does anyone have
any ideas?

Thanks.

John

On Tue, 28 Jul 2009 11:38:12 -0700 (PDT), John wrote:

Normally, when I create a query that contains any of those related
tables, Access automatically adds the appropriate joins in the query.
This was happening fine up until today. Now, it doesn't add any of the
joins, even though the relationships are set up fine in the back end.
I haven't made any changes in either the front or back end, so I don't
know why it has suddenly stopped working.

I've tried various things, such as relinking all the tables,
compacting and repairing both database files, etc. Does anyone have
any ideas?

Hit it with a baseball bat g...

Open the frontend. Use Tools... Options and be sure that "Track Name
Autocorrect" is UNCHECKED (this feature has richly earned the nickname Name
Autocorrupt). If it was checked, compact the database after unchecking it.

Delete ALL the table links.

Use File... Get External Data... Link and relink to the tables in the backend.

Compact and repair, and see if that doesn't help.

--

John W. Vinson [MVP]

On Jul 29, 2:27*am, John W. Vinson
wrote:
On Tue, 28 Jul 2009 11:38:12 -0700 (PDT), John wrote:
Normally, when I create a query that contains any of those related
tables, Access automatically adds the appropriate joins in the query.
This was happening fine up until today. Now, it doesn't add any of the
joins, even though the relationships are set up fine in the back end.
I haven't made any changes in either the front or back end, so I don't
know why it has suddenly stopped working.

I've tried various things, such as relinking all the tables,
compacting and repairing both database files, etc. Does anyone have
any ideas?

Hit it with a baseball bat g...

Open the frontend. Use Tools... Options and be sure that "Track Name
Autocorrect" is UNCHECKED (this feature has richly earned the nickname Name
Autocorrupt). If it was checked, compact the database after unchecking it..

Delete ALL the table links.

Use File... Get External Data... Link and relink to the tables in the backend.

Compact and repair, and see if that doesn't help.

--

* * * * * * *John W. Vinson [MVP]

Hi,

I looked at that option in the front end, and it was already
unchecked. However, it was checked in the back end. I'm not sure if
it's supposed to be checked there or not (I don't really know what the
option does), but I unchecked it there, compacted and repaired the
back end, then re-linked all tables linked in the front end and
compacted and repaired the front end. It didn't fix the problem.

What would happen if I set up relationships in the front end? Would
those relationships override the ones set up in the back end? I know I
can't enforce referential integrity in the front end, but as long as
those don't override the back end, the integrity should be taken care
of by the back end relationships.

I suppose that this issue isn't really that big of a deal. The
database still functions the way it's supposed to (at least, I haven't
noticed any difference yet). I just find it annoying when things just
randomly happen and I don't know why or how to fix them.

On Wed, 29 Jul 2009 05:05:19 -0700 (PDT), John wrote:

I looked at that option in the front end, and it was already
unchecked. However, it was checked in the back end. I'm not sure if
it's supposed to be checked there or not (I don't really know what the
option does), but I unchecked it there, compacted and repaired the
back end, then re-linked all tables linked in the front end and
compacted and repaired the front end. It didn't fix the problem.

:-{(

What would happen if I set up relationships in the front end? Would
those relationships override the ones set up in the back end? I know I
can't enforce referential integrity in the front end, but as long as
those don't override the back end, the integrity should be taken care
of by the back end relationships.

I don't see how that would help. The frontend relationships should in fact
inherit from those in the backend, and I don't know how you would manage two
(in your case duplicate but in principle distinct) sets of relationships!

I suppose that this issue isn't really that big of a deal. The
database still functions the way it's supposed to (at least, I haven't
noticed any difference yet). I just find it annoying when things just
randomly happen and I don't know why or how to fix them.

I'm baffled too.
--

John W. Vinson [MVP]

John wrote:
Normally, when I create a query that contains any of those related
tables, Access automatically adds the appropriate joins in the query.
This was happening fine up until today. Now, it doesn't add any of the
joins, even though the relationships are set up fine in the back end.
I haven't made any changes in either the front or back end, so I don't
know why it has suddenly stopped working.

In your Access options, do you have "Enable AutoJoin" checked for query
design?

I think that feature does not depend on relationships, but it's the only
thing I can suggest which comes close to the problem you described.