deleting relationships in access

S

sassorclass333

I'm trying to delete a relationship in from tables and I can't seem to be
able to do so, I'm very frustated have asked many others, without luck.
Please if anyone is able to help.
 
M

Maurice

What's the problem you are encountering? If there is data in the tables you
might not be able to delete. In that case you have to delete the many side of
the relation first to be able to delete the relation. This brings me to the
question why you want to delete the relationship in the first place..
 
6

'69 Camaro

Hi.
I'm trying to delete a relationship in from tables
Click to expand...

The simplest way in the GUI is to open the Relationships window and delete
the line between the primary key in the table on the one side and the
foreign key in the table on the many side. The simplest way in code is to
drop the foreign key constraint on the table on the many side.

If you're doing it programmatically, use a SQL query to drop the constraint,
but you must use the name of the foreign key constraint, which is stored in
the MSysRelationships table. If you used the GUI to create the
relationship, then the name of the constraint will be the name of the table
on the one side concatenated with the name of the table on the many side.
For example:

ALTER TABLE tblManySide
DROP CONSTRAINT tblOneSidetblManySide;

Beware that you should not add or delete any records in the two tables while
the relationship is missing, or you could end up with orphan records, i.e.,
children in the table on the many side with no parent in the table on the
one side.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
 
P

Pieter Wijnen

This is dependant on the Access version
Querying MSysRelationships is the fail-safe path

Pieter
 
Top