Deleting associated records from linked tables

[AlexD]

Hi,

I currently have 2 SQL tables linked in Access 2K. It is a one-many relationship, a main company details table linked to a multiple contacts table.

Have created a form that looks at both tables and can alter details and delete individual contacts.

However, now I'm trying to implement a function that will delete a company AND its associated contacts at the same time (with the use of a command button). I can delete the company and the contacts will obviously no longer appear on the form but they will be ophaned in the contacts table which is not ideal.

Anyone have any ideas how I can do this - any help appreciated.

cheers,

Alex

 

[Mile-O]

In the Relationships window, Enforce referential integrity and select Cascade Delete Records

 

[AlexD]

Mile,

'Enforce Referential Integrity' along with 'Cascade Delete Related Records' check boxes are greyed-out. Do you know if there is any reason for this?

 

[maxmangion]

in the relationship window do not go Relationship -> Edit Relationships because in that way it is going to be greyed out as you say.

in the relationship window Right click on the line between the two tables you have related and choose edit relationship from their ... the enforce referential integrity will not be greyed out.

 

[Mile-O]

Yes, there's a good reason. I never saw that the tables were linked. As I don't know how SQL works (although I'm sure its not that different; just better) you can either set up such a condition there, or use a delete query to remove the records before you delete the main one.

 

[AlexD]

Chaps,

thanks for that although I'm still having the same problem with the greyed out boxes. As a test I did a make-table on the links to create the 2 tables in Access format, and found it then allowed me to enforce integrity.

So maybe Access has an problem forcing integrity with linked tables?

 

[Mile-O]

So maybe Access has an problem forcing integrity with linked tables?

As I said above.

 

[AlexD]

Thanks Pat,

Put me on the right path.

If anyone's interested for future reference, just set the relationship between the 2 tables in SQL Enterprise Manager (by right-clicking the Primary Key column).

You can then delete as normal in Access and this will cascade delete all associated records in the linked tables (no need for triggers, at least as far as I know).

cheers,

Alex