By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,970 Members | 1,668 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,970 IT Pros & Developers. It's quick & easy.

How to break a connection between tables

P: 10
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?
Jul 29 '10 #1

✓ answered by 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...regardless 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

Share this Question
Share on Google+
3 Replies


patjones
Expert 100+
P: 931
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...regardless 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
Jul 30 '10 #2

P: 10
@zepphead80
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.
Aug 2 '10 #3

NeoPa
Expert Mod 15k+
P: 31,769
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.
Aug 2 '10 #4

Post your reply

Sign in to post your reply or Sign up for a free account.