How do I 'DROP' and unamed relationship/constraint in MS Access with SQL?

Question

I have a Microsoft Access database and I have two tables. Table1 has a primary key and Table2 has a foreign key that references Table1's primary key. This relationship is set up and viewable in the Relationship viewer in MS Access, the 'Enforce Referential Integrity' check box is checked, and the Join type is an inner join. The relationship is:

[Table1]--1---------N--[Table2]

I need to be able to 'DROP' this relationship/constraint via SQL. How do I do this? I have no name for this relationship/constraint as it was set up in Access manually, not with SQL. Is there a way to do what I need to do?

Answer 1

Determine the relationship using

SELECT szRelationship FROM Msysrelationships WHERE szObject = 'childtablename' and szReferencedObject = 'parenttablename'

THEN

Use the ALTER TABLE command. Something along the line of this

ALTER TABLE Table2 DROP CONSTRAINT Relation1

Answer 2

To drop a relationship named with a GUID, as relationships created in the relationship window are named, you need to use square brackets, like so:

ALTER TABLE TableName 
DROP CONSTRAINT [{0992AADA-3921-4AC0-8E95-745A87709D91}]

---

It is not too difficult to find the name of a relationship using the system table MsysRelationships, the columns are:

ccolumn
grbit
icolumn 
szColumn    
szObject    
szReferencedColumn  
szReferencedObject  
szRelationship

In your case, the name will be a GUID, say {A869FC34-81AF-4D29-B81D-74180BF73025}

You can also use VBA and ADO schemas to list relationships.

If you would like to say what is available to you, it will be easier to suggest a suitable method to get the name.

EDIT in VBA

Sub ListRelations()
Dim rel As DAO.Relation

For Each rel In CurrentDb.Relations
    Debug.Print rel.Name
    Debug.Print rel.ForeignTable
    Debug.Print rel.Table
    For Each fld In rel.Fields
        Debug.Print fld.Name
    Next
Next
End Sub

Answer 3

If you can see it in the relationship viewer, you can click on it and delete it from there.