Relationships Question 1

[missymarie1014]

This is Access 2000. I am working with a split database. Is there any significance to the fact that the relationship window is different when I access the mdb file directly as opposed to accessing the relationships window by opening the database program in edit mode (holding down the shift key while opening). At first I was concerned, but everything is working fine. Is there any significance to these different relationship views or is this because I developed the database as one file and only recently split the database. Perhaps after splitting the database the relationships window attached to the raw tables ceases to be updated. Is that a possible explanation? Any changes I make to the relationships window in the program don't seem to be reflected when I open the mdb file directly. Thanks for any information!

 

[lameid]

I think what you are saying is that when you look at your relationships in each file they look different (arranged differently).

You cannot create relationships in your front end, you have to do it in your back end (unless you have unlinked tables in your 'FE' for some reason) because you have to create them on tables and not linked tables.

That said, when you link the tables the relationships are available for access to read. However, the LAYOUT of the relationships is saved in each file. In the Front End you might need to go into the relation ship window and hit the multi-table looking Icon to show all relationships (or in the backend for that matter). The difference in layout is nothing more than rearranging the tables. Just like rearranging tables in the query designer makes no difference, it makes no difference for relationship layout.

 

[missymarie1014]

Thanks for your post lameid!
Actually my relationships window looks different between the front end and the back end. My front end relationship window has a table that my back end does not have. This is a table I added to the relationships window after I split the database. It is properly linked to a back end table, but I specified the table in the relationships window while working in edit mode in the front end, and the back end does not show that table in the back end relationships window. And the link works properly because when I look at the data in the table, I can drill down to the table that has the relationship specified. It is as if after I split the database, the links take over and if I establish a relationship in the front end, it doesn't need to automatically flow to the back end at that point. The links make all that work. A little wierd, but I guess it makes sense. I started looking at all this because I wanted to change the name of one of my back end tables without losing the link to the front end and I have experimented with several approaches to this and none has worked. I cannot delete the front end table link and reestablish it because there are queries and program code referencing the front end table and some of that would become corrupted if I delete the link table on the front end. When I have tried changing the back end table name, I lose the link from my front end table. Is there any technique where you can manually modify the linked names in order to implement a name change on a back end table without having to mess with the front end table, all the while preserving the link to the new name table in the back end?

 

[lameid]

Weird... I assumed you couldn't create a relationship because you can't delete one that exists in the backend... You cannot set referential integretity or cascade updates and deletes with linked tables (makes sense).

The name problem... If Auto-correct is turned off, and as a general rule you should turn it off, then you can easily fix this.

The simplest way in my opinion is to change the backend table name:
Ensure Name Autocorrect is off.
Next delete the link in the front end. Link to the table with the new name.
Rename the relinked table name to the old name.

Everything should work fine.

On the otherhand, if autocorrect were on, you could rename the link first turn autocorrect off, fix the backend name and relink. This would fix the queries and forms but not vba and is generally frowned uppon for this shortcoming.


I would still make all relationships in the backend unless you had a good reason not to. That way anyone that wants to do adhoc reporting off your backend with his own frontend sees the relationships. More importantly, you are explicitly setting your referential integrity.

 

[missymarie1014]

lameid,
Thanks for your guidance on these issues. I completed the table name changes just as you directed and all went fine. Did some experimenting and determined that you can input relationship changes in the front end and these changes will not be seen in the back end. You can also input relationship changes in the back end and these changes flow automatically to the front end if the affected tables are already linked. This makes a lot more sense now and it would more reasonable to make the relationship change in the back end and let it flow forward. One last question....
Why do you encourage the permanent disabling of the auto correct feature? It seems to provide some work saving and integrity if you do a name change or other modification. I am just interested in knowing if there is some pitfall I should be concerned about. You have probably been around the block a few more times than I have. Thanks again.

 

[lameid]

This is probably worth a read...

http://www.allenbrowne.com/tips.html

But to specifically answer your question about auto-correct you'd need to scroll down to the flaws in Access Section and find the link for auto-correct or check the link below.

http://www.allenbrowne.com/bug-03.html