Why delete a Relationship?

Sometimes you will need to delete relationships that you have already defined in Microsoft Access. It could be that these relationships are no longer valid or you need to build new relationships or it could even be that you have entered it by mistake. Whatever be the reason, to delete a relationship, in relationships document tab, you need to delete the relationship line.

Take care of Referential Integrity

Take care to delete any referential integrity that you might have defined for that relationship. If you don’t take care of this, there will be ‘orphan’ records created unnecessarily.

Steps to delete a relationship

  • Open the Microsoft Access Database. If you have the database already open and you are trying to delete a relationship, make sure you have all the tables associated to the relationship closed. Because, you will not be able to delete the relationship if it is being accessed by any other user or process.
  • Open the Relationship document tab from Database Tools Tab by clicking Relationships in the Show/Hide group.
  • Display All Relationships that have been defined on all tables in the database by clicking on All Relationships in the Relationships group on the Design tab. You cannot see the relationships defined on the hidden tables unless you select to ‘Show hidden objects’ in the Navigation Options box. Hidden tables will have ‘Hidden’ option checked in the properties box.
  • Select the relationship you wanted deleted by clicking on the relationship line. The selected line will be highlighted.
  • You can delete the highlighted line either by the delete key or by right clicking on it and then clicking delete option
  • It will ask you for confirmation by displaying a message similar to ‘Are you sure you want to do make delete this relationship permanently from your database’?” You need to make sure that you have selected the right relationship that you want deleted.
  • You can confirm the delete by pressing YES and you will have your relationship deleted from the MS Access database. If you do not want to delete that, then select NO.

How to delete it through a DAO program?

There is also another option available. You can delete a relationship programmatically using DAO. DAO stands for Database Access Object library and this is a product of Microsoft. In order to delete using this method, you have to know the exact name of the relationship. Once you know the relationship name you can delete it by executing the following command

CurrentDb.Relations.Delete “Enter the name of the Relationship Here”

If you do not know the exact relationship name to be deleted there is a procedure you can execute which can identify the relationship name for you.