Updating all tables in a many to many relationship

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I am using Access 2003. I have 3 tables, joined in a many to many
relationship. i.e.

Table 1 -> Link Table (2) <- Table 3

I have added the relationships in the 'Relationships' window, and used the
wizard to create a form based on the 3 tables, showing data from Table 1, and
its related data from table 3.

When I update or add data to the form, data is updated/added to tables 1 and
3, but the link table does not update.

Since many to many relationships are common, this must be a common problem,
but I cant seem to find the answer either in the help/tutorials or on the web.

If someone could point me in the right direction I would be very obliged.
Many thanks,
Gary.
 
Hello,

I have tested the issue on my side and it works fine. When defining a
Many-To-Many Relationships, did you select the Referential Integrity?

Enforce referential integrity
Cascade Update Related Fields
Cascade Delete Related Records

For your reference, I tested it by performing the following steps:

1. Create three tables which have a Many-To-Many Relationships:

table_one link_table table_two
id-------------->t1id |-->id
stuff t2id-------- | stuff

2. Check the Enforce referential integrity:

Cascade Update Related Fields
Cascade Delete Related Records

3. Follow the form wizard to create a new form. Select three tables and
select "Linked forms" when creating the form.

The following article is for your reference:

304466 Defining relationships between tables in a Microsoft Access database
http://support.microsoft.com/?id=304466

I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Sophie - thank you for replying and many apologies for the lateness of this
post, (work has been a bit hectic, sorry!)

Anyhow, I tried what you suggested and sure enough it works. Many thanks for
this. I wonder, is it possible to make a form/subform arrangement work,
rather than linked forms. This would be more intuitive for my users. For
instance:

Table A contains Recipes, (Autonumber primary key, and (say) a Name field,
and mixing/cooking instruction field.

Table B called, say, RecipeIngredient, contains foreign keys of both the
other tables, and an amount in grams of the ingredient. (Its the link table
for the many to many)

Table C contains Ingredients, with an Autonumber primary key, a Name field,
and maybe optional dietary information on this ingredient.

So, linking the tables just like you suggest, could you make a form/subform,
that allowed users to input new Recipes, and input the ingredients for that
recipe on the subform, (and maybe add new ones if not present in the
database), and also input the amount of that ingredient for this particular
recipe, and have all the tables update properly?

I have tried to do this, but I still run into problems. Hope that is clear,
and again, many thanks in advance for any help/tips,
regards,
Gary
 
Hi Elizabeth,

'Sophie' replied to my original post, and I have just asked her a further
question, (see original post), but I can always keep you updated if you like.
My email is (replace the "at" for the @ symbol) jn976rfgc"at"tesco.net.

regards,
Gary
 
Hello Gary,

I test it again and it works fine:

In the form wizard, select the three tables, then select "Form with
subforms" option. Other options are the default. It create two forms: one
main form and one subforms.

When openning the main form and entering some data, we need to enter the
data in the table one and the table two first, then enter data in the link
table because we have defined constraints on it.

You may test it on your side. I hope the information is helpful.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Sophie,

Again many thanks for the efforts to answer my questions, and sorry again
for the late reply, (I still have too much work, so I can't complain really
<grin> )

Anyhow, I will try this now I have a moment to myself. I think my main
problem was not realising that you need to get data into the two 'main'
tables before you can set up the information in the link table, due to the
comstraints.

I will give it a go later this evening. Many thanks for all the help,
regards,
Gary
 
Hello,

You are welcome. If anything is unclear, get in touch.

Sophie Guo
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security

=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top