How can I protect and reconstruct relationships? 2

[nagyf]

I have a rather complicate database.
The relationships can be easily deleted with a few mouse clicks. That is very dangerous fotr the integrity.
I exported the MSysRelationships to a table of an external database in more readable form. (I made individual fields from the attribute bytes of the relations.)
I can compare the existing relations with the stored ones. But how can I reconstruct them? The MSysRelationships is not updatable. I guess I have to write a module which handles the Relations collection. I do not want to waste my time to reinvent the wheel. Is there anybody in the Forum who has such a module?
TIA [tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]

 

[mp9]

Something along these lines will work. This example creates a relationship between tblTravel.TravelID and tblAccom.TravelID and sets it to automatically cascade updates and deletes:

.
.
.
Dim dbs As Database, rel As Relation, fld As Field
Set dbs = DBEngine(0)(0)
Set rel = dbs.CreateRelation ("TravelID1", "tblTravel", "tblAccom", dbRelationUpdateCascade + dbRelationDeleteCascade)
Set fld = rel.CreateField("TravelID")
fld.ForeignName = "TravelID"
rel.Fields.Append fld
dbs.Relations.Append rel
dbs.Relations.Refresh
Set dbs = Nothing
.
.
.

Note that each relatinoship you create in this way has to have a unique name - I've used TravelID1 in this example.

 

[nagyf]

Have you tried
1. Join table A and B by two fields in each table a1,a2-b1,b2?
2. Copy the corresponding records of the MSysRelationships to a table CrucialRelationShips?
3. Delete the relationships made in Step#1?
4. Restore the relationships using the table made in Step#3, using a code like in your mail?

[tt]
Ferenc Nagy
|\ /~ ~~|~~~ nagyf@alpha0.iki.kfki.hu Fax: (36-1)-392-2529 New!
| \ | | Institute of Isotope and Surface Chemistry
| \ | -+- 1525 Bp. POB 77. Tel. 36-1)-392-2550
| \| |

http://alpha0.iki.kfki.hu/~nagyf

`-' ' `-' "The goal of the life is the struggle itself"
[/tt]