Relationship: Turning Off Referential Integrity Enforcement via VBA?

PeteCresswell wrote in

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"(PeteCresswell)" wrote in
:

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.

I think it's just bat**** 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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Per David W. Fenton:
I think it's just bat**** 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.
--
PeteCresswell

"(PeteCresswell)" wrote in
:

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.

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 http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"(PeteCresswell)" wrote in
:

Per David W. Fenton:
I think it's just bat**** 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.

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 bat**** crazy -- this is
just not the kind of thing that should be attempted while a data
file is in use.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

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

"(PeteCresswell)" wrote in
:

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.

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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/