Delete a constraint from code?

[jacobh]

Hi.

I've created a relation (referential integrity) between to tables manually
in Access.

Now I need to delete the relation/constraint (because I need to delete a
field), but I don't know the name of the constraint.

I need to delete the field "MyField" - by executing SQL queries like

ALTER TABEL MyTable DROP CONSTRAINT MyXXX
ALTER TABEL MyTable DROP MyField

How can I find in Access, where I can see the name of MyXXX? Or is there a
way through the ADO interface to learn the names of relations associated
with a certain field?

I created the field and the relation myself, but now copies of the database
is in use many places, so I need a way to do this by code.

Thanks for any help!

Jacob

 

[Brendan Reynolds]

The following code requires a reference to the ADOX (Microsoft ADO Ext. 2.x
for DDL and Security) object library. Alternatively, if your app already has
a reference to the DAO object library, you may find it easier to use DAO,
there's an example of that below too. Alternatively again, you could try
adapting the code at the following KB article which uses the ADODB
OpenSchema method. Replace 'adSchemaIndexes' in the sample code with
'adSchemaForeignKeys'.

http://support.microsoft.com/default.aspx?scid=kb;en-us;185979

Public Sub ListAllRelations()

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As ADOX.Key
Dim col As ADOX.Column

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
For Each ky In tbl.Keys
If ky.Type = adKeyForeign Then
Debug.Print "Table: " & tbl.Name
Debug.Print "Key: " & ky.Name
Debug.Print "Related Table: " & ky.RelatedTable
For Each col In ky.Columns
Debug.Print "Column: " & col.Name
Debug.Print "Related Column: " & col.RelatedColumn
Next col
Debug.Print
End If
Next ky
Next tbl

End Sub

Public Sub ListRelationByColumn(ByVal ColumnName As String)

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As ADOX.Key
Dim col As ADOX.Column
Dim boolFound As Boolean

Set cat = New ADOX.Catalog
Set cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
For Each ky In tbl.Keys
If ky.Type = adKeyForeign Then
boolFound = False
For Each col In ky.Columns
If col.Name = ColumnName Then
boolFound = True
Exit For
End If
Next col
If boolFound Then
Debug.Print "Table: " & tbl.Name
Debug.Print "Key: " & ky.Name
Debug.Print "Related Table: " & ky.RelatedTable
For Each col In ky.Columns
Debug.Print "Column: " & col.Name
Debug.Print "Related Column: " & col.RelatedColumn
Next col
Debug.Print
End If
End If
Next ky
Next tbl

End Sub

Public Sub ListRelationByColumn2(ByVal ColumnName As String)

Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field
Dim boolFound As Boolean

Set db = CurrentDb
For Each rel In db.Relations
boolFound = False
For Each fld In rel.Fields
If fld.Name = ColumnName Then
boolFound = True
Exit For
End If
Next fld
If boolFound Then
Debug.Print "Table: " & rel.Table
Debug.Print "Relation: " & rel.Name
Debug.Print "Related Table: " & rel.ForeignTable
For Each fld In rel.Fields
Debug.Print "Column: " & fld.Name
Debug.Print "Related Column: " & fld.ForeignName
Next fld
Debug.Print
End If
Next rel

End Sub

 

[Jacob Havkrog]

Thanks Brendan.

That was very helpful.

If the latest version of ADO in installed ona machine, is ADOX then also
installed? Can I be sure it's there for me to use?

You don't happen to have some code that demonstrates how to add a contraint
using ADOX, re my latest posting.

Best regards
Jacob

 

[Brendan Reynolds]

The only supported way to install any of the ADO components is to install
the MDAC redistributable, so in theory if one component is installed they
should all be installed. But I mostly use DAO, I don't think I've got any
widely-deployed apps using ADOX, so I don't have personal experience in that
area.

I don't think I've seen the latest post to which you refer. I'll try to keep
an eye out for it.