Removing a table relationship (1 Viewer)

G'day,
I had a relationship between my Employee file and another 1-Many file (tblJobTitle) in my relationship diagram. So when I open the Employee file I have records with a plus (+) sign on them. When I click the + on one of the records I can see the related tblJobTitle records.

After some thinking about the design I decided that I did not need the relationship between the two tables so I deleted the 1-Many table without first deleting the link between the two tables.

However when I open the Employees file I still see the + on the LHS of each record and if I click on same the old tblJobTitle records are still there.

I am wondering what I need to do to destroy the old relationship?

put your table in design view.
on its property Subdatasheet Name, set it to [None].

I decided to make a copy of the tblJobTitle table called tblJobTitlecopy and then deleted the original table "tblJobTitle"
Then I reopened the employee table and the + signs were still there, but when I clicked on same I got the following error message
[The table or query name "tblJobTitle" you entered in either the property sheet or macro is misspelled or refers to a table or query that doesn't exist]

After thinking about it I renamed my backup copy "tblJobTitleCopy" back to "tblJobTitle" and the Employee file opens with the + sign on each record and links back to the tblJobTitle records.

So in a nutshell I am stumped how to remove the old relationship or subrecords from my Employee Table.

arnelgp said:

put your table in design view.
on its property Subdatasheet Name, set it to [None].

Click to expand...

Thank you ever so much. I did not know that there was a property sheet for the table. Setting it to "None" did the trick!

Please ignore my second post and many thanks

goodluck with your project!

That didn't remove the relationship. It just changed the subdatasheet property.

To actually remove the relationship, open the relationships window and press the show relationships icon. The table you deleted will reappear with the relationship. You have to click on the relationship line and delete that to actually delete the relationship. Then you can delete the tables from the window. Don't forget to also remove the foreign key from the child table to complete the process.

If you have the AutoCorrect feature enabled ( it is enabled by default), you will find that Access will "help" you by assigning the old relationships to the new table name and it goes downhill from there. You would be better off turning off the AutoCorrect feature and then turning it on ONLY when you want to rename a table or a column of a table AND you want Access to help you to propagate the change. Then you have to actually open every object that the change will effect since the changes are not propagated immediately. They are propagated only the next time an object is opened. Once you have opened and closed all affected objects, turn off NameAutoCorrect.

I attatched two files. The doc file is a copy of a MS article (including the link to the original article which may no longer be valid) and the other is a summary of the article as a PP presentation I made for our local Access Users Group.

Pat is absolutely correct in stating that removing a subdatasheet does not remove the relationship that may exist between the tables

However when creating a subdatasheet, you will be asked whether you want a relationship to be created between the tables. So its possible no relationship exists anyway!

My advice would be to never use subdatasheets. Although it may seem convenient to have 'related' info all visible from one place, they can be confusing to end users especially if you have several 'cascading' subdatasheets. Their use also causes apps to run slower as each table has to be loaded into memory when a form is opened rather than just the one table.

For a detailed article about relationships & their use with referential integrity (together with further details on subdatasheets), see this 3 part article on my website: Relationships Advice