Relationship: Turning Off Referential Integrity Enforcement via VBA?

I can get DB.Relations.Relation, but cannot figure out what prop to
change in order to turn off referential integrity enforcement.

My agenda is that I'm writing a throw-away VBA routine to convert a DB
from one architecture to another.

Desired bc in the course of development we'll be converting the most
recent DB over-and-over again and the the list of changes seems tb
long enough to justify developing a routine to do it.

BTW, seems pretty obvious that it's one of the "Attributes" and the
value sb whatever's behind dbRelationDontEnforce.

But I guess my real question is how do I get at the existing
attributes one-by-one?

You need to futz with the Attributes property of the relationship.

From the Help file:

For a Relation object, the value specifies characteristics of the
relationship represented by the Relation object and can be a combination of
these constants.

Constant Description

dbRelationUnique The relationship is one-to-one.
dbRelationDontEnforce The relationship isn't enforced (no
referential integrity).
dbRelationInherited The relationship exists in a non-current
database that contains the two linked tables.
dbRelationUpdateCascade Updates will cascade.
dbRelationDeleteCascade Deletions will cascade.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"PeteCresswell" wrote in message
...
I can get DB.Relations.Relation, but cannot figure out what prop to
change in order to turn off referential integrity enforcement.

My agenda is that I'm writing a throw-away VBA routine to convert a DB
from one architecture to another.

Desired bc in the course of development we'll be converting the most
recent DB over-and-over again and the the list of changes seems tb
long enough to justify developing a routine to do it.

"PeteCresswell" wrote in message
...
BTW, seems pretty obvious that it's one of the "Attributes" and the
value sb whatever's behind dbRelationDontEnforce.

But I guess my real question is how do I get at the existing
attributes one-by-one?


The constants defined by RelationAttributeEnum are bit flags that can be
ANDed with the Relation.Attributes value to determine which of them is in
effect. For example:

'----- start of code -----
Sub ListRelations()

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim strAttributes As String

Set db = CurrentDb
For Each rel In db.Relations
Debug.Print rel.Name, rel.Attributes, rel.Table, rel.ForeignTable, _
rel.Properties.Count

strAttributes = vbNullString
If rel.Attributes And dbRelationUnique Then
strAttributes = strAttributes & "One-to-one"
Else
strAttributes = strAttributes & "One-to-many"
End If
If rel.Attributes And dbRelationDontEnforce Then
strAttributes = strAttributes & ", Don't enforce"
End If
If rel.Attributes And dbRelationInherited Then
strAttributes = strAttributes & ", Inherited"
End If
If rel.Attributes And dbRelationUpdateCascade Then
strAttributes = strAttributes & ", Cascade updates"
End If
If rel.Attributes And dbRelationDeleteCascade Then
strAttributes = strAttributes & ", Cascade deletes"
End If
If rel.Attributes And dbRelationLeft Then
strAttributes = strAttributes & ", Left join"
End If
If rel.Attributes And dbRelationRight Then
strAttributes = strAttributes & ", Right join"
End If
Debug.Print , "Attributes: "; strAttributes
For Each fld In rel.Fields
Debug.Print , "Field: "; fld.Name, _
"ForeignField: "; fld.ForeignName
Next fld
Next rel

End Sub
'----- end of code -----

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Per Dirk Goldgar:
The constants defined by RelationAttributeEnum are bit flags that can be
ANDed with the Relation.Attributes value to determine which of them is in
effect. For example:

'----- start of code -----
Sub ListRelations()

What I visualize, then is attributes as a binary
of something like six bits.

e.g.

001110


Question: If I'm happy with all but one of the bits (the one that
controls RI enforcement), what's the syntax for overlaying it via
dbRelationDontEnforce without clobbering any of the other bits?

Maybe .Attributes = .Attributes And dbRelationDontEnforce ?
--
PeteCresswell

On Tue, 09 Sep 2008 19:09:38 -0400, "(PeteCresswell)" wrote:

Maybe .Attributes = .Attributes And dbRelationDontEnforce ?

Try

..attributes = .Attributes XOR dbRelationDontEnforce

This will set it if it's clear, and clear it if it's set.

Use OR to set it (even if it's already set), or OR NOT to clear it regardless
of its setting.
--

John W. Vinson [MVP]

Enforced referential integrity is really a foreign key constraint on two
tables. To turn it off you drop the foreign key constraint, and to turn it
back on you recreate the foreign key constraint. Examples:

Turn off referential integrity between tblParent and tblChild:

currentdb.execute "ALTER TABLE tblChild " _
& "DROP CONSTRAINT tblParenttblChild", dbfailonerror

(note tblParenttblChild is the default name of the constraint listed in
msysrelationships where the parent table name is concatenated with the child
table name, but if you created the constraint name use that name instead)

Turn on referential integrity between tblParent and tblChild:

currentdb.execute "ALTER TABLE tblChild " _
& "ADD CONSTRAINT tblParenttblChild " _
& "FOREIGN KEY (parentprimarykey) " _
& "REFERENCES tblParent (parentprimarykey)", dbfailonerror


Chris
Microsoft MVP


PeteCresswell wrote:
I can get DB.Relations.Relation, but cannot figure out what prop to
change in order to turn off referential integrity enforcement.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200809/1

currentdb.execute "ALTER TABLE tblChild " _
& "DROP CONSTRAINT tblParenttblChild", dbfailonerror

I'm going with this one even though it deletes the entire relationship
instead of just altering it's nature.



I can't make XOR work.

1060 With targetDB.Relations("tlkpTradingAccounttblSecurity" )
1061 .Attributes = .Attributes Xor dbRelationDontEnforce
1069 End With

Seems tb acting like I don't have permission: "3219: Invalid
operation."

I got the name via copy/paste from an enumeration of Relation.Name,
so I'm pretty sure it's spelled correctly.

Yup, looks like Dirk, John & I all missed the fact that the Help file
explicitly states "For an appended Relation object, the Attributes property
setting is read-only."

However, if you're already going through the Relations collection to get the
names of the relations, why not just delete them there?

Dim dbCurr As DAO.Database
Dim lngLoop As Long

Set dbCurr = CurrentDb()
For lngLoop = (dbCurr.Relations.Count - 1) To 0 Step -1
dbCurr.Relations.Delete dbCurr.Relations(lngLoop).Name
Next lngLoop
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"PeteCresswell" wrote in message
...

currentdb.execute "ALTER TABLE tblChild " _
& "DROP CONSTRAINT tblParenttblChild", dbfailonerror

I'm going with this one even though it deletes the entire relationship
instead of just altering it's nature.



I can't make XOR work.

1060 With targetDB.Relations("tlkpTradingAccounttblSecurity" )
1061 .Attributes = .Attributes Xor dbRelationDontEnforce
1069 End With

Seems tb acting like I don't have permission: "3219: Invalid
operation."

I got the name via copy/paste from an enumeration of Relation.Name,
so I'm pretty sure it's spelled correctly.

Per Douglas J. Steele:
However, if you're already going through the Relations collection to get the
names of the relations, why not just delete them there?

Dim dbCurr As DAO.Database
Dim lngLoop As Long

Set dbCurr = CurrentDb()
For lngLoop = (dbCurr.Relations.Count - 1) To 0 Step -1
dbCurr.Relations.Delete dbCurr.Relations(lngLoop).Name
Next lngLoop
Set dbCurr = Nothing

That makes sense - especially since I would then have the option
of re-creating a relationship without the RI enforcement in cases
where I wanted the little lines tb be there in the Relationships
window even though they wouldn't mean anything.
--
PeteCresswell