none
Deleting and Recreating Relationships RRS feed

  • Question

  • Hi all

    Relative newbie/occasional user here!

    I have an inherited Access Database originally created in Access 2000, but now with up-to Access 2016 clients.

    The problem I have is that our company is moving to SAP and therefore certain fields in our DB will need to change to accommodate the new length/format.

    The split database is used primarily to control drawing management, revision status etc.

    Looking at the back end database tables I see that a mask has been applied to limit the length and to force a leading "C" character to be included in a Contract Number field.

    Can I simply delete this mask and increase the field length to allow input of our new required short text format?

    The next part I am particularly wary of... one of the fields that I need to change has a relationship to another table.  I cannot change the format of this field without deleting the relationship and recreating it after the change.

    Will this effectively remove the relationship for all existing records and mean that they only apply to data input after the change?  The relationship is one-to-may with referential integrity applied if that makes a difference.

    Am I on a road to total ruin here, or should this be doable without serious downstream effects.  I realise that some input and results forms will need modifying to accommodate the longer field length.

    Thanks

    Phil

    Monday, July 31, 2017 11:38 AM

Answers

  • If you want to change the field length, rather than just the allowed format, you will have to delete and recreate the relationships, I'm afraid.  You won't lose anything by this, though; the existing rows in the referencing table will still map to the correct rows in the referenced table.  Take a screen shot of the (neatly laid out) relationships window beforehand as an aide-mémoire.

    PS:  I was rather surprised to find this is the case, as you can create a relationship on two text columns with different Size properties.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, July 31, 2017 5:08 PM Postscipt added.
    • Marked as answer by TheHC Monday, July 31, 2017 6:39 PM
    Monday, July 31, 2017 4:57 PM

All replies

  • 1) Yes, you can safely delete the input mask and increase the field size of the field.

    2) Note the properties of the relationship that you need to remove.

    Delete the relationship.

    Edit the properties of the field(s) involved in the table(s).

    Then create the relationship again, and set the properties as they were before. Do this before entering new data into the table(s).


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 31, 2017 12:36 PM
  • You should not need to delete the relationships.  If you need to change existing data, ensure that in the relationship dialogue 'cascade updates' are enforced.  You can then change the values of the key in the referenced table (that on the 'one' side of the relationship type) and the changes will automatically be made to matching rows in the referencing table.  You'll first need to remove any constraints on the format of the columns in both tables of course.

    If you are not making any changes to existing data, but merely allowing for future data, then removing the constraints on the format of the data in both the referenced and referencing tables should suffice.  Rows in the referencing table will still map to the relevant rows in the referenced table, for both the old and new data.

    It goes without saying that all tables should be backed up before undertaking these sorts of changes.

    Ken Sheridan, Stafford, England

    Monday, July 31, 2017 12:38 PM
  • Thanks for your input Ken and Hans

    Ken, I have tried the approach you have outlined in the second paragraph of your post as I wish to leave existing data as-is.  The issue seems to be with changing the field length.  It appears that I can delete the Input Mask OK - this exists in the one-side table only.  BUT, when I try to alter the field length I am getting the alert message: The setting you entered isn't valid for this property. It makes no difference whether I go into the one-side table or the many-side table, the alert message is presented the same.

    The field I am trying to change is a Short Text field in both tables with a current defined Field Size of 5.

    If I do delete then recreate the relationship, will the existing records lose that relationship and will it only apply to data entered after the change?

    Thanks

    Phil

    Monday, July 31, 2017 4:32 PM
  • If you want to change the field length, rather than just the allowed format, you will have to delete and recreate the relationships, I'm afraid.  You won't lose anything by this, though; the existing rows in the referencing table will still map to the correct rows in the referenced table.  Take a screen shot of the (neatly laid out) relationships window beforehand as an aide-mémoire.

    PS:  I was rather surprised to find this is the case, as you can create a relationship on two text columns with different Size properties.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Monday, July 31, 2017 5:08 PM Postscipt added.
    • Marked as answer by TheHC Monday, July 31, 2017 6:39 PM
    Monday, July 31, 2017 4:57 PM
  • Deleting the relationship between two tables in itself doesn't affect existing records in any way.

    But it will allow you to edit existing records or create new records that violate referential integrity. That's why I recommended re-creating the relationship immediately after changing the field size.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Monday, July 31, 2017 5:25 PM
  • Thanks again Ken and Hans.  Your support with Access is and has been invaluable more times than I can remember.

    Phil

    Monday, July 31, 2017 6:42 PM