Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2005
    Posts
    23

    Unanswered: Disable Relationship 2007 VBA

    Hi,

    I need to temporarily disable a relationship in my database.

    I found this code but it does not work:

    CurrentDb.Relations(RelationName).Attributes = _
    CurrentDb.Relations(RelationName).Attributes + dbRelationDontEnforce
    Of course when you debug print the relationship name it is a hex string in 2007

    CurrentDb.Relations("{FA3BB80E-113A-4B2B-B480-DBCF0ECBABBD}").Attributes
    Runtime error 3219
    Invalid Operation
    The reason I need to do this is because I am seeding a child table with a parent ID but I am seeding it while the parent record is not updated so I get a ref integ issue.

    Thanks for any and all help!

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm not sure I understand why you'd want to break your referential integrity..?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yeah, sounds like a bad move to me.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Aug 2005
    Posts
    23
    It is a temporary break. I was inserting an autonumber for a current parent into a child. This attempt was performed while the parent record is still being inputted but not updated.

    Therefore it would not let me do that until the record in the parent went through a full update. I would get a referential integrity error.

    If there was a simple way to update the current record while in process I would have tried it. No luck.

    If you have a better solution than let me know. It would be appreciated.

    Anyway for anyone who is googling on how to turn off a relationship and later add it please see the code below:

    To Turn It On:
    Dim SQL1 As String
    Dim cmd As New ADODB.Command

    SQL1 = "ALTER TABLE PolicyTransaction ADD CONSTRAINT ProjectMainPolTran FOREIGN KEY (ProjectIDKey) " & _
    " REFERENCES ProjectMain (ProjectIDKey) ON DELETE CASCADE ON UPDATE CASCADE;"

    cmd.ActiveConnection = Application.CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = SQL1

    cmd.Execute

    To turn it off
    Dim SQL2 As String
    Dim cmd As New ADODB.Command

    SQL2 = "ALTER TABLE PolicyTransaction DROP CONSTRAINT ProjectMainPolTran;"

    cmd.ActiveConnection = Application.CurrentProject.Connection
    cmd.CommandType = adCmdText
    cmd.CommandText = SQL2

    cmd.Execute

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So add the parent record before adding the child record... don't screw around with relationships to do data entry.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •