Deleting a Relationship using VBA 1

[LSTAN]

Hi,

How does one delete a relationship between two tables by using VBA??

Also is there a way to test the availability of a servers??
(using APIs???)

Thanks and regards

LSTAN

 

[lxn]

Yeah, this problem bothered me for a bit as well when I had to do it. What you have to do is, find out the name of the relationship you want to delete, and then use the code: 'objDB.Relations.Delete (relationship name string)' to delete it. If you know the name of the relationship, it's ez, but since you are probably using access, than it probably automatically assigned it a relationship name, and that's where the trouble is.

I think this is the query that I used to find out all the relationship names of any give table in the DB, not totally sure:

SELECT MSysRelationships.szRelationship
FROM MSysRelationships
WHERE (((MSysRelationships.szReferencedObject)=[Table Name?]));

I hope this will help a bit, good luck!

-- Merlin

 

[LSTAN]

Thanks Merlin,

But I already have it figured out, this is what i did

Function deleteRelation()

Dim myDB as Database, i as Integer, myRel as Relation
'
Set myDB = CurrentDB()
'
For Each myRel in myDB.Relations
If myRel.Table = "tblShip" And myRel.ForeignTable = "tblCargo" Then
myDB.Relations.Delete myRel.Name
Else
Exit Function
End if
Next myRel
'
For Each myRel in myDB.Relations
If myRel.Table = "tblShip" And myRel.ForeignTable = "tblDestination" Then
myDB.Relations.Delete myRel.Name
Else
Exit Function
End if
Next myRel
'
End Function

Thank you for your time.

Regards
LSTAN

 

[lxn]

Hehe nice, well that works the same way, there's only one problem I see. According to you code, you are deleting all relatoinships existing between certain two tables right? What if I only want to delete only one particular relationship (for the reason of making DDL patch to correct mistake in existing DB, or maybe structural change, and so on)? In this case, you'd still have to find out the Name of the specific relationship.
I like what you've done, much more simpler in your case. It's just that you are gonna need a way to find out relationships' names sometimes, and only way I know is using that query. Unless you know another way to do it that u wanna show me

best of luck.