using code to delete relationships (1 Viewer)

[planetcomics]

what is the code that will allow you to search through the objects in your database, find a certain key or relationship, then delete it?

 

[Pat Hartman]

Here's code to delete a relationship:

Sub SubDelRelations()
Dim Myrel As Relation
Dim MyDB As Database

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
MyDB.Relations.Delete "RelComp_CompCode"
End Sub

Here's code to make a relationship:
Sub SubMakeRelations()
Dim Myrel As Relation
Dim MyDB As Database
Dim MyFld As Field

Set MyDB = DBEngine.Workspaces(0).Databases(0)

'Company -->> Company_Code
Set Myrel = MyDB.CreateRelation("RelComp_CompCode")
Myrel.Table = "dbo_EC_Company"
Myrel.ForeignTable = "dbo_EC_Company_Code"
Myrel.Attributes = 0
Set MyFld = Myrel.CreateField("Company_ID")
MyFld.ForeignName = "Company_ID"
Myrel.Fields.Append MyFld
MyDB.Relations.Append Myrel
End Sub

This is a query that will delete an index:
ALTER TABLE dbo_EC_CIS_Number_Xref DROP CONSTRAINT NaturalKey;

 

[planetcomics]

That's how I used to do it in DAO, but I want to write everything using ADO in Access 2000. Below is the code that searches for a table and then deletes it along with it's relationships. I want to be able to search just for a relationship and delete it.


Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim key As ADOX.key
Set cat = New ADOX.Catalog

cat.ActiveConnection = CurrentProject.Connection
[FrmCustomers Subform].SourceObject = "FrmDummy"
For Each tbl In cat.Tables
If tbl.Name = "TblPullList" Then
cat.Tables("TblReserve").keys.Delete "PullsReserve"
cat.Tables("TblWantList").keys.Delete "PullswantList"
cat.Tables("TblSpecialOrders").keys.Delete "PullsSpecialOrders"
cat.Tables.Delete "TblPullList"
End If
Next tbl

 

[Pat Hartman]

Isn't there a relations collection is Access 2000?

Why would you loop through collections anyway when you know the name of the item you want to update? It just adds overhead. Your update is hard coded. It's not like you are modifying multiple items with the same code.