How to break a connection between tables

[BobS]

I originally built two tables to have a relationship between a field (call it Monitor asset) on table one to the primary key on table 2. When I deleted the relationship using Access's relationship screen, when my form reads though the 1st table sequentially, it will not display records where the field Monitor asset does not have a matching primary key on table 2. I tried compacting & repairing the db's thinking the link is still there somehow. Any suggestions?

[patjones]

I think this is something that causes confusion for a lot of people. This may or may not be what is causing your problem but it is one possibility...

Breaking the relationship in the relationship window won't necessarily change the underlying recordsource for the form. Suppose you have tblA and tblB in your database, which are not related according to the relationships window. If you then create a query in design view (or equivalently a SQL statement written in the form's recordsource property) which involves a JOIN between tblA and tblB, the resulting records will be constructed according to the JOIN...regardle ss of what you see in the relationships window.

My point is that you should examine the form's recordsource to see what is going on. I would say that might shed light on the issue.

Pat

[BobS]

I think this is something that causes confusion for a lot of people. This may or may not be what is causing your problem but it is one possibility...

Breaking the relationship in the relationship window won't necessarily change the underlying recordsource for the form. Suppose you have tblA and tblB in your database, which are not related according to the relationships window. If you then create a query in design view (or equivalently a SQL statement written in the form's recordsource property) which involves a JOIN between tblA and tblB, the resulting records will be constructed according to the JOIN...regardle ss of what you see in the relationships window.

My point is that you should examine the form's recordsource to see what is going on. I would say that might shed light on the issue.

Pat

Thanks Pat, you hit the problem right on the head. It is amazing how easy it is when you know where to look for the problem. Couldn't find anything in write ups about it in Access.

[NeoPa]

Just to clarify, the relationships defined simply provide a default way to link tables together in a query. If two tables witha relationship defined are added into a query then the wizard will assume a link. If a form is built upon that query then the form will also reflect that link.

That is the closeness of the connection though. Not remotely a direct connection at all. Be very careful before assuming connected tables have anything to do with a defined relationship.