Help - Deleting Relationships - Cause a prob?

[Carrie241]

I want to delete a relationship temporarily so that I can delete a record. After the record is deleted I will restore the relationship immediately. But I am concerned that this will ruin any queries I have already established or ruin my other records.
Could you let me know if this is possible without making a mess of things that have already been established.

Thanks
Carrie

 

[boblarson]

Carrie:

Welcome to Access World Forums.

Why do you need to delete a relationship to delete a record? That doesn't sound right.

 

[ted.martin]

If ever you are not sure the consequences of what you are doing, take a copy of the whole file; then if it all goes pear-shaped, you can just revert to the copy and start again.

 

[Carrie241]

Why I want to delete a relationship

When I try to delete the record I get a message
The record cannot be deleted because the table 'SampData' includes related records.

So I thought if I delete the relationship I can delete the record from both tables. Re-establish the relationship - and then hopefully everything is fine

Any suggestions

 

[boblarson]

The question you gotta ask, is what related records. Now, if you set up the relationships incorrectly so that you are actually linked to a lookup table, then you really do need to delete the relationship, but you do not add it back. If SampData is not a lookup table and actually has data related to this other record then you need to delete those records first and then delete the other (unless you had cascade deletes set and then by deleting the one record in the one table it would delete the related records in the other table). But, if you remove the relationship and delete this one record, then your SampData table will have orphan records, unless of course as I already stated if it is a lookup table.

So, that is a short description of things and the thing to note is that by deleting the relationship and then deleting the one record and then trying to add the relationship back, if it really is related data (related only to that deleted record), then trying to re-establish the relationship will likely fail due to the orphan records.

So, you can try by using Ted's suggestion - use a COPY to test with before doing it on the live database and keep a backup copy of your database as well.

 

[Carrie241]

The record was actually the last record entered- an ID number was wrong
I want to delete the record and recreate it same infor just a different ID number.

 

[Carrie241]

The relationships are correct I just want to get rid of one of the records
the records are added everyday

 

[rainman89]

then y not go into the table and merely change the ID?

 

[Carrie241]

No unfortunately it won't let me it says it is related to other records

 

[boblarson]

No unfortunately it won't let me it says it is related to other records

That's what I said. You will have to get rid of the other, related records FIRST, before you can delete this one OR change the Foreign Key for those records.

 

[Carrie241]

Sorry, but I am new to Access so it takes a while to sink in

So this is what I did:
Took each relationship that was connected deleted the line so there was no relationship.
Went into each table that had the info
deleted the record.
Re-established the relationship. And everything seems okay

Thanks for your help

Since the guy that developed the database is no longer with us I will be working on it- So I am sure I will be asking for help a few times

 

[Pat Hartman]

Deleting relationships to delete records is not the answer. You need to determine the purpose of the relationship and understand the constraints it is enforcing. From your description of the error message, you could have deleted the "child" records first and then you would have been able to delete the "parent" record. Going through this procedure would have given you an understanding of what the relationship is for. It is possible that the relationship should be defined with cascade delete specified. This option is commonly used in a hierarchial structure such as OrderHeader/OrderDetail. when you delete an Order, there is no point in keeping the order details so you would want to "cascade" the delete from the Order header to include all of its child details also.