MS Access - Deleting a relationship that can't be found.

Question

I am trying to change a text field to a number field, however, any attempt to make changes is met with the same error. Apparently this field is part of a relationship. I've looked through everything. The relationship window shows no connection to this field. I have selected the show "All Relationships" button, but still nothing. Another similar question stated that the relationship was found in a report, but I have not yet created any reports. I could just copy everything into a new project, or even just delete and recreate the one table, but if anyone can help me out, I'd much rather learn something here.

Answer 1

You can try the following:

Besides clicking on "All Relationships" in the relationship view, add your tables manually and see if any new relationships show up.

Another nice way to find out about all dependencies of your table click on it and choose "Object Dependencies" under Database Tools. You could have created relationships in Queries, in Forms (Data Sources) and in Reports - just check the dependent objects.

Answer 2

You could use VBA to examine the Relations collection. I don't know if it will identify your missing relationship, but it should be easy to find out.

Paste this code into a standard module, run it, and review the output in the Immediate Window:

Public Sub InspectRelations()
    Dim rel As DAO.Relation
    Dim fld As DAO.Field
    For Each rel In CurrentDb.Relations
        Debug.Print "Relationship Name: " & rel.Name
        Debug.Print "Table: " & rel.Table
        Debug.Print "ForeignTable: " & rel.ForeignTable
        For Each fld In rel.Fields
            Debug.Print "Field Name: " & fld.Name
            Debug.Print "ForeignName: " & fld.ForeignName
        Next fld
        Debug.Print String(10, "-")
    Next rel
    Set fld = Nothing
    Set rel = Nothing
End Sub