Linking multiple tables

[Genotix]

Hi Guys,

I've got a problem that has kept me busy for quite some time now (like four days!!).

I'm not very fimiliar with Access and a bit fimiliar with Visual Basic 6

I've got a database that holds three tables.
1. Recipe
2. Component (A recipe consists of components)
3. TT_Recipe_Component (This table holds the link between a certain Recipe and it's components)

***ATTRIBUTES***
[Recipe]
NUMBER (PK)
Code

[Component]
NUMBER (PK)
Description
Code
Package_Type

[TT_Recipe_Component]
NUMBER (PK)
Recipe_NUMBER (FK -> Recipe.NUMBER)
Component_NUMBER (FK -> Component.NUMBER)


I use the following query to fill my recordset :
CODE
SELECT * FROM ((TT_Recipe_Component
LEFT JOIN Component ON TT_Recipe_Component.Component_NUMBER = Component.NUMBER)
LEFT JOIN Recipe ON TT_Recipe_Component.Recipe_NUMBER = Recipe.NUMBER)
WHERE TT_Recipe_Component.Recipe_NUMBER IN
(SELECT NUMBER FROM Recipe WHERE CODE = '" & Recipe_List.Code & "')

Now when I delete a record from that recordset, I would like the "delete" query to delete ONLY the link from the
TT_Recipe_Component table.
Instead of just doing that the query (cascades) also
deletes the linked Component from the Component table and the Recipe form the Recipe table.

How can I delete a record without having to create a new
recordset with a seperate connection to TT_Recipe_Component?
(In plain SQL I guess I'd just have to mention: "ON DELETE NO ACTION")

Linux IS userfriendly.
It's only very selective about who it's friends are.

 

[HitechUser]

Genotix,

Are you using Cascade Deletes when you define your table relationships??

http://support.microsoft.com/default.aspx?scid=kb;en-us;304474

 

[willir]

Genotix

Have you considered "blanking" the value of the foreign key before the delete.

Richard

 

[Genotix]

HitechUser, yes I am.

Richard, that's a good one you've mentioned!
Didn't quite think of that one..

I've retrieved the information that I should enter the following properties to the recordset :

      .Properties("Unique Table") = "TT_Recipe_Component"

This works!

I think clearing the forein key fields should also work, as you've mentioned.

In my opinion it's pretty strange that the recordset doesn't check the relation of the access database. (Since I've set it correctly it should consider binding to that constraints and NOT using it's own.)

Linux IS userfriendly.
It's only very selective about who it's friends are.