INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VB ACCESS turn off Referential Integrity

VB ACCESS turn off Referential Integrity

(OP)
I am doing a Right Join Update Query that errors out because I have Referential Integrity turned on.

I need to turn it off

Run Update Query 1 then Run Update Query 2

Then turn it back on.

Does anyone know how to turn this feature off and on with VB code?

RE: VB ACCESS turn off Referential Integrity

There is no point in having referential integrity if you turn it off to run queries. If you do not want it, don't include relationships, if you do want it, don't mess with it.

forum705: Microsoft: Access Modules (VBA Coding) would be more suitable for this question.

RE: VB ACCESS turn off Referential Integrity

(OP)
I have two table that require referential Integrity.  (We are using several other software programs to populate this access project. - This project is tieing all of our departments together)  Each day I am running an update query over night to bring this access program to a current state for all to see.  

Updating records is NOT a problem.  It's when a NEW acct is made.  When I am doing this update I need to add a record into several tables before anyone can use it.  Once it's added then I want Referential Integrity in place.  

So I need to turn it off.  Add the record to the tables then turn it back on.  I could do this manually but I would really rather it be automatic!

I realize this isn't the ideal way of doing this, but it is the easiest way at this time.  (I am using the same table structure that are coming out of these other software program.  Which is creating some one to one relationships as well as one to many.)

 

RE: VB ACCESS turn off Referential Integrity





It would seem to me that if you were to load your tables in the proper sequence, low-level to high-level, that this would not be an issue.

Quote:

(I am using the same table structure that are coming out of these other software program.  Which is creating some one to one relationships as well as one to many.)

This is not a true statement!
You have, by virtue of your Relationships, already created, "some one to one relationships as well as one to many". The key is the SEQUENCE of adding data.

Skip,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

RE: VB ACCESS turn off Referential Integrity

I agree with Remou and Skip ... you need to get the sequence correct. You really don't want to do an append with RI turned off and then discover that you can't turn it back on because your tables are now in violation.

Anyway ... if you must

CODE

CurrentDb.Relations(RelationName).Attributes = _
CurrentDb.Relations(RelationName).Attributes + dbRelationDontEnforce
to turn it off and

CODE

CurrentDb.Relations(RelationName).Attributes = _
CurrentDb.Relations(RelationName).Attributes - dbRelationDontEnforce
to turn it back on.

"RelationName" is the name of the relation that you want to change. You can get the Relation Names from

CODE

    Dim db As DAO.Database
    Dim rf As DAO.Relation
    
    Set db = CurrentDb()
    For Each rf In db.Relations
        Debug.Print rf.Name, rf.Table, rf.ForeignTable
    Next
    Set db = Nothing
End Sub

RE: VB ACCESS turn off Referential Integrity

(OP)
Thank you I will Try That

My Problem comes with the One to One Relationship.  Each table comes from a different dept.  I really could combine the 2 tables into 1 but it was just easier to pull them both in as is. (this happens nightly - the only common info is the KEY which is GOOD))

I probably really don't need referential integrity in this case, but I want the Casecade Delete to work. (If you delete an acct in this dept also delete it here)  No matter which way I try to update Table A before Table B or vice Versa  I keep getting the KEY VIOLATION error and it won't append the records.  Turning off Ref Intg worked though.

Is there any other work around, other than the above code, or combining the tables?  Am I missing Something?

Thank you!  I really Appreciate the input!

 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close