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...b;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
--
Brendan Reynolds
Access MVP
"jacobh" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>
>
>
>
Similar Threads
