I need your help!
I have created a DB with 4 tables, USERS, RECORDS, EQUIPMENT, and SERVICES. When a user registers then logs in they can place an advert in RECORDS, EQUIPMENT, and SERVICES tables. I have created an Admin area where I can delete users and Adverts separately from the DB.

What I'm wanting to do is when I delete a user I want it to delete all their adverts from where ever they've posted them.

eg. A user logs in and posts an advert in the RECORDS and EQUIPMENT table. When I delete the user from the USERS table I want it to delete the Adverts form the RECORD and EQUIPMENT table.

Thanxs

I did this a while ago, so can vaguely remember how to do it, though I can't recall the details. This should however get you going...

In your DB you need to ensure you have set up the relationships between the tables (TOOLS > RELATIONSHIPS). I can't remember if ACCESS prompts you for the next part when you establish relationships, but what you need to do then is edit the relationships and tick the 'Enforce Referential Integrity' box and the tick the 'cascade delete related records'. This should achieve your aim.

Have a look in the ACCESS help for more info on 'Referential Integrity'. I hope this helps get you started.



UD4 | ASP | VBScript | ACCESS | IIS5

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I did this a while ago, so can vaguely remember how to do it, though I can't recall the details. This should however get you going...

In your DB you need to ensure you have set up the relationships between the tables (TOOLS &gt; RELATIONSHIPS). I can't remember if ACCESS prompts you for the next part when you establish relationships, but what you need to do then is edit the relationships and tick the 'Enforce Referential Integrity' box and the tick the 'cascade delete related records'. This should achieve your aim.

Have a look in the ACCESS help for more info on 'Referential Integrity'. I hope this helps get you started.



UD4 | ASP | VBScript | ACCESS | IIS5
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Thanxs sesame, I'll give that a go

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
I did this a while ago, so can vaguely remember how to do it, though I can't recall the details. This should however get you going...

In your DB you need to ensure you have set up the relationships between the tables (TOOLS &gt; RELATIONSHIPS). I can't remember if ACCESS prompts you for the next part when you establish relationships, but what you need to do then is edit the relationships and tick the 'Enforce Referential Integrity' box and the tick the 'cascade delete related records'. This should achieve your aim.

Have a look in the ACCESS help for more info on 'Referential Integrity'. I hope this helps get you started.



UD4 | ASP | VBScript | ACCESS | IIS5
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Thanx for that mate, I now have it working.

Hi Wayne,

How about writing a tutorial on this for the competition?

Martha Graham

Hey why don't you share it with the rest of us because I'm trying to do the same thing, and the relationship in access didn't help. So how do you delete a record related to another record in another table.

So does anyone know how to do this or what?

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
So does anyone know how to do this or what?
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I'll try to explain how I did it.

In my DB I have 4 tables, USERS, EQUIPMENT, RECORDS and SERVICES. In the USERS table there is a U_ID (user id) field that is the unique field. In my original DB the other 3 tables DID NOT have a U_ID field.

To input the data in to the tables use 4 separate forms one for each of the tables.

1/ I had to modify my DB so that every table in the DB had a U_ID field. The U_ID field in the EQUIPMENT, RECORDS and SERVICES table is not unique.

2/ Then I had to change the page that inputs the data in to the DB to include their U_ID, I did this by using a hidden form field to insert their U_ID in to the table. Now when a user inputs data via a form in to the EQUIPMENT, RECORDS or SERVICES table their U_ID is also input in the Newly created U_ID field.

3/ Then I had to create a relationship between the 4 tables in the DB. I created the relationship between the U_ID field in the USERS table and the U_ID field in the other 3 tables. So now all the data in the EQUIPMENT, RECORDS and SERVICES tables are related (linked) to the USERS table via the U_ID field. Make sure you click Enforce Referential Integrity, and Cascade Update Related Fields and Cascade Update Related Records

what this means: <b>Select Enforce Referential Integrity</b>, and then select <b>Cascade Update Related Fields</b> to automatically update corresponding values in the related table whenever you change a primary key value in the primary table.

Select <b>Enforce Referential Integrity</b>, and then clear <b>Cascade Update Related Fields</b> to prevent changes from being made to a primary key value in the primary table whenever there are related records in the related table.

4/ Now that all the tables are related to the USERS table via the U_ID field when you delete a user form the DB all their entries form the other tables are also deleted.

I hope this was some help or at least gets you started.

Wayne1000

Thanks wayne, I'll give it a go right now.

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
Thanks wayne, I'll give it a go right now.
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
CajunFox, did you manage to get it working?

be aware of where your relationships go with cascading deletes, you might end up losing data you wanted to keep, like half your database and you might loose alot of your content, so be very sure it is what you want (need).

Not really usefull feedback, I know...but I didn't know that would happen and lost half my database that way, getting it all back wasn't funny <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle>

With kind regards,

Dennis van Galen
Webmaster KPN Services
Financial and Information Services