Relationship: Turning Off Referential Integrity Enforcement via VBA?

[PeteCresswell]

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]

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?

 

[Douglas J. Steele]

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.

 

[Dirk Goldgar]

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 -----

 

[(PeteCresswell)]

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 ?

 

[John W. Vinson]

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.

 

[PeteCresswell]

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.

 

[Douglas J. Steele]

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

 

[(PeteCresswell)]

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.

 

[David W. Fenton]

m:

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.

Why not import into a set of buffer tables without the RI and leave
the original alone?

 

[David W. Fenton]

Per Douglas J. Steele:

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.

I think it's just batshit crazy to be doing this at all. Just use
buffer tables without the RI and don't bother with this kind of
nutso mucking around with schema structure.

 

[(PeteCresswell)]

Per David W. Fenton:

I think it's just batshit crazy to be doing this at all. Just use
buffer tables without the RI and don't bother with this kind of
nutso mucking around with schema structure.

There's method in this particular madness.

I'm in the process of a significant architectural change to the
app. Adding the ability to track something called "Ladders" in
the financial world. Adding tables, adding fields, adding
relationships, migrating data into the new tables/fields.... and
so-forth.

I *could* just make all the mods by hand - but working the list
takes a good hour and, of course, there's a chance of human error
at every step.

Given that it took about five hours to develop the code and I'll
be converting the latest prod DB for testing way more than five
times - and that the guys administering the prod environment at
least technically have the right to demand an automated update of
the prod DB - it seems like a reasonable use of time.

The RI removal part came bc one table seems tb maxed out
index-wise To add a recursive relationship for parent/child
records, I had to wipe a couple of the seemingly less-critical
other RI enforcements.

 

[(PeteCresswell)]

Per David W. Fenton:

Why not import into a set of buffer tables without the RI and leave
the original alone?

I'm doing that too - but for the permanent tables we still need
RI changes.

 

[David W. Fenton]

Per David W. Fenton:

I'm doing that too - but for the permanent tables we still need
RI changes.

I guess I just don't understand the situation, then. I'd think you'd
use the buffer tables to massage the data to conform to the RI
rules. That's sort of the point!

 

[David W. Fenton]

Per David W. Fenton:

There's method in this particular madness.

I'm in the process of a significant architectural change to the
app. Adding the ability to track something called "Ladders" in
the financial world. Adding tables, adding fields, adding
relationships, migrating data into the new tables/fields.... and
so-forth.

I *could* just make all the mods by hand - but working the list
takes a good hour and, of course, there's a chance of human error
at every step.

Given that it took about five hours to develop the code and I'll
be converting the latest prod DB for testing way more than five
times - and that the guys administering the prod environment at
least technically have the right to demand an automated update of
the prod DB - it seems like a reasonable use of time.

The RI removal part came bc one table seems tb maxed out
index-wise To add a recursive relationship for parent/child
records, I had to wipe a couple of the seemingly less-critical
other RI enforcements.

Why not just massage the data to meet the RI requirements in a
buffer tables, then append all the data to an empty copy of the MDB
with RI in place, and replace the original data file with this new
one.

The only objections I can see to this would be:

1. the back end is replicated. But in that case, you couldn't do
this anyway, as you'd have to be doing it in the Design Master in
granular steps (and, yes, you'd have to take out the RI, synch,
update the data to conform to new RI, synch, and then put the RI
back in and synch one last time).

2. the back end is in use when you're doing these updates. If that's
the case, then once again, I'd say you're batshit crazy -- this is
just not the kind of thing that should be attempted while a data
file is in use.

 

[(PeteCresswell)]

Per David W. Fenton:

I guess I just don't understand the situation, then. I'd think you'd
use the buffer tables to massage the data to conform to the RI
rules. That's sort of the point!

But, ongoing, I'd like to have RI enforcement.

 

[David W. Fenton]

Per David W. Fenton:

But, ongoing, I'd like to have RI enforcement.

I'm only suggesting removing it for a buffer MDB where you massage
the data to make it conform to the new rules, then append that fixed
data to an entirely new MDB with your new RI rules.

So, I don't see how my suggestiong doesn't meet your requirement
here.