VB ACCESS turn off Referential Integrity VB ACCESS turn off Referential Integrity hext2003 (TechnicalUser) (OP) 22 Jul 08 15:25 I am doing a Right Join Update Query that errors out because I have Referential Integrity turned on. I need to turn it offRun Update Query 1 then Run Update Query 2Then 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 Remou (TechnicalUser) 23 Jul 08 06:55 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 hext2003 (TechnicalUser) (OP) 23 Jul 08 08:41 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 SkipVought (Programmer) 23 Jul 08 08:55 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,Just traded in my old subtlety...for a NUANCE! RE: VB ACCESS turn off Referential Integrity Golom (Programmer) 23 Jul 08 11:51 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 mustCODECurrentDb.Relations(RelationName).Attributes = _CurrentDb.Relations(RelationName).Attributes + dbRelationDontEnforceto turn it off andCODECurrentDb.Relations(RelationName).Attributes = _CurrentDb.Relations(RelationName).Attributes - dbRelationDontEnforceto turn it back on."RelationName" is the name of the relation that you want to change. You can get the Relation Names fromCODE 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 = NothingEnd Sub RE: VB ACCESS turn off Referential Integrity hext2003 (TechnicalUser) (OP) 23 Jul 08 12:07 Thank you I will Try ThatMy 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!