2 votes: ***       3,942 views      0 comments
by Allen Browne, 05 September 2006    (Access 95+)

Microsoft Access Tips for Serious Users

Provided by Allen Browne,

Delete All Relationships

Warning: This code is dangerous! It deletes all relationships in the current database.

In some circumstances, Access 95 would fail to display relations between tables in the Relationships window. Since you could not view the relationships, you could not delete them, so your database was inconsistent. This code is provided as a fix.

Function DeleteAllRelationships() As String
' WARNING: Deletes all relationships in the current database.
    Dim db As Database      ' Current DB
    Dim rex As Relations    ' Relations of currentDB.
    Dim rel As Relation     ' Relationship being deleted.
    Dim iKt As Integer      ' Count of relations deleted.
    Dim sMsg As String      ' MsgBox string.

    sMsg = "About to delete ALL relationships between tables in the current database." & vbCrLf & "Continue?"
    If MsgBox(sMsg, vbQuestion + vbYesNo + vbDefaultButton2, "Are you sure?") = vbNo Then
        DeleteAllRelationships = "Operation cancelled"
        Exit Function
    End If

    Set db = CurrentDb()
    Set rex = db.Relations
    iKt = rex.Count
    Do While rex.Count > 0
        Debug.Print rex(0).Name
        rex.Delete rex(0).Name
    DeleteAllRelationships = iKt & " relationship(s) deleted"
End Function

Copy and paste this function into a module. Press Ctrl+G to open the Immediate Window. Enter:

? DeleteAllRelationships()

You will then have to rebuild all your relationships from scratch.

Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent

This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'DeleteAllRelationships()'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

E-mail (e-mail address will be kept private)
Verify Code
Verification Code

It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.