Front end relationships don't show

[WalkieTalkie]

I understand that relationships belong to the tables, which are in the back end, and the front end is linked to those tables, so inherits those relationships. Hopefully I've got that much right.

I have an just created my first Access 2003 database and split it into Front end and back end. The problem is that when I look in the Front End's relationship window, no relationships are showing, even if I click <Add all>. (The relationships are showing beautifully in the back end) Furthermore, when I make a new query using the query design grid in the front end, the tables there don't have any relationships. I have to create the relationships in the query design grid as well.

(I've tried updating and refreshing the linked tables, no joy. Have also tried starting again from scratch, using the the database splitter to split front and back end - still same problem)

I have made lots of databases in the past, using Access 97 and have never come across this problem. Is it me or is there something I'm missing?

 

[kjv1611]

I'm pretty sure that in a split database, you will never see the relationships in the front end, so long as the original data resides on the back-end. This makes perfect sense, b/c the tables should all be in the backend (if they are, then that is also where the relationships should be), whereas your forms and modules should be in the front end, which do not require relationships.

 

[WalkieTalkie]

So is it correct that when I'm in the front end, and I use the query design grid to make a new query, and I add tables to the design grid, those tables do not have relationships automatically showing in the design grid window? I thought in previous databases I've made, those relationships were already there. I do not remember ever having to add the relationships in the design grid.

 

[TheAceMan1]

How are ya WalkieTalkie . . .

You can go ahead an relink them (manually) in the frontend. When you do [blue]the inheritance of the backend won't let you make any mistakes![/blue] and they will be inherited!

You can prove this by trying to delete a relationship in the frondend once its established . . . the inheritance won't allow you to do so! . . .

[blue]Your thoughts?[/blue]

See Ya! . . . . . .

 

[WalkieTalkie]

Hiya AceMan!

Sorry to be so thick, but what do you mean by:
"You can go ahead an relink them (manually) in the frontend. When you do the inheritance of the backend won't let you make any mistakes! "

How do I relink them manually in the front end? Do you mean I create them just as you normally would with relationships? And do you mean in the relationships window or in the query design grid?

(Why haven't I ever had to do this before? Is MSAccess 2003 different in this regard to 97?)

Thanks for your help

 

[TheAceMan1]

WalkieTalkie . . .

The same way you would in the [blue]relationships window![/blue]

See Ya! . . . . . .

 

[WalkieTalkie]

So is it simply that the relationships are there, but I just can't see them unless I go and look at them in the back end? I just have to trust that they are there, but just invisible in the front end?

 

[TheAceMan1]

WalkieTalkie . . .

Maybe I'm not explaining it right.

You can setup your relationships in the FrontEnd exactly as they are in the BackEnd. When you do . . . you'll be [blue]inheriting relationships[/blue]. You can't make a mistake because inheritance of the backend won't allow it!

I agree with you . . . We need to see the relationships in the front end! . . . and relinking rlationships in the FrontEnd accomplishes this! . . . they just have to match whats in the BackEnd! . . . or inheritance won't allow it!

I hope this is clearer! . . .

See Ya! . . . . . .

 

[WalkieTalkie]

I'm afraid its not much clearer.

I would have thought that if something has been inherited then you wouldn't need to set it up again.

"You can't make a mistake because inheritance of the backend won't allow it!" I have tested this by creating a relationship in the front end that does not exist in the back end - and nothing stops me from doing this.

"they just have to match whats in the BackEnd! . . . or inheritance won't allow it!" I don't understand what this means. As above - I have created relationships in the front end that do not match the back end, and nothing stops me doing this.

Sorry I don't understand you - I wish I did!

 

[TheAceMan1]

WalkieTalkie . . .

In several of my DB's I had the need to see the relationships in the frontend during design time. So I duplicated exactly.

When I was done and no longer needed them, attempting to delete gave a message [blue]the relationship is inherited and can't be deleted[/blue].

The point of all this is if you need to see them in the front end you can make it so.

As for autojoins look in options tables/queries!

See Ya! . . . . . .

 

[podcar]

WlkieTalkie -- I am new to this forum, but have joined because I am having the exact same problem you were having. The relationships established in the back end DB, which contains only the data, are not showing up in the front end DBs and are not showing in query design view.

It seems from this thread that the solution was not clear to you perhaps. Have you found a solution? Perhaps one that does not require re-establishing all the relationships in the front end relationships window?

Many thanks for your post.

 

[WalkieTalkie]

Hi podcar
No, I never did get to the bottom of it. The problem was:
1. The relationships never show automatically in the front end relationships window.
2. In order to see what the relationships between the back end tables are while you are in the front end, you can create new relationships in the FE relationship window. However, as far as I have been able to figure, there is nothing to stop you making a mistake whilst doing this, and creating a relationship that is not only different from those in the back end, but can even be contradictory. This seems fraught with danger, so I have avoided doing this.
3. When creating a query in the front end query design grid, relationships between tables seem to show up automatically sometimes and not others.

I never successfully resolved the problem of how to see what the relationships are whilst in the front end. I guess you could say I gave up and hoped like anything that the relationships were really maintaining referential integrity like they are supposed to!

Please let me know if you can shed any light on this...

 

[podcar]

WalkieTalkie --- Thanks for your reply. I am not surprised that you have let this one go, at least for now. What you describe is exactly what I have found. I have a client DB system I had set up several years ago [Access 2002] in which there are about 5 front end [FE] DBs linked to the single back end [BE] DB. Now they want to be able to do their own queries. Yet, just as you describe, the table relationships show up in the query design view on some FEs but not on others, even though they are all using the same version of Access. A real mystery. And I don't want to manually re-create the BE table relationships in the FEs since that is a lot of work and may be fraught with errors.

But this is a problem for me in providing the functionality they need for their staff. I intend to try to figure this out, both through some research in the MS Knowledge Base or other web sources and/or through experimentation on their DB system.

I will let you know what I find, sucessful or not. It may be a week or two. I'll get back to you.

Thanks!

 

[WalkieTalkie]

Hi again
Yes, I can see it is a problem for you, whereas for me I have been able to just let it go and hope for the best. I'd be really interested though to see what you do manage to come up with.
Good luck!

 

[podcar]

WalkieTalkie -- I apologize for taking so long to respond. During my visits to my client's office in November and December I was Not able to resolve the problem such that the FE DBs would automatically include the BE DB table relationships in their query design views. I tested this on 3 FE DBs, including the one FE DB that did show the BE DB table relationships. Two of these 3 PCs use Access 2002, and 1 uses Access 2003. Two of them use Windows XP OS and one uses Windows 2000 OS. The one FE DB which does show the BE table relationships is the one which resides on a PC with Windows 2000 OS. The BE DB resides on their server which uses Windows 2000 OS for server. All Access DBs [FE and BE] are Access 2000 format. So...it appears to me that the differences in OSs is what is hanging this functionality up in Access. IE, the only one that works has the same OS as the server PC where the BE DB resides.

Out of the 8 PCs [users] that have FE DBs it turned out that only 3 more experienced users were going to be doing their own queries. So I got around the above problem by setting up what I called a "common query" [CQ] FE DB, with usual links to the BE DB tables, and putting this FE DB right on the server in the same folder as the BE DB is in. I then put shortcuts to this CQ FE DB on the desktops of the PCs of those 3 users. Since the DB resides on the same PC as the server [with same OS of course] it works fine, that is, it shows the BE table relationships in the query design view window. Another benefits is that these 3 users can utilize each others custom queries.

Cheers == Podcar