Deleting associated records from linked tables

AlexD

Registered User.
Local time
Today, 19:30
Joined
Sep 20, 2002
Messages
35
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
:)
 
In the Relationships window, Enforce referential integrity and select Cascade Delete Records
 
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?
 
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.
 
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.
 
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?
 
AlexD said:
So maybe Access has an problem forcing integrity with linked tables?

As I said above. ;)
 
Referential integrity is enforced by the database server. You cannot specify RI from within Access for linked SQL tables. You MUST declare the RI in the SQL server database. I don't remember if you can specify Cascade delete for SQL server, if you can't, you'll need to write a trigger to perform the cascade operation.

In an Access database, you can only declare RI for local tables. If you have linked tables, you must declare the RI in the table's source db.

Do not attempt to circumvent this with coding in Access. Fix the RI at the source. If you don't know how or don't have administrator permission, ask your DBA to do it for you. If you know how, you could write the appropriate DDL statement and use a pass-through query to execute it if you don't have SQL Server Enterprise Manager installed on your PC. Or if push comes to shove, you can create an .adp, link to the appropriate database and make the changes through that GUI. It is not as easy to use as Enterprise Manager but I think it does everything.
 
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
 

Users who are viewing this thread

Back
Top Bottom