Updating tables

[shankbanta]

This is the brain dead question of the day.

I have the following tables:
CPU
Monitor
Workstation
Dept

Now, if I create a Query using all of those tables I can create one form that will update all these tables? AT least that is what I thought. When I use the form it indicates that the recordset could not be updated. Am I just forgetting something? Is it Friday yet?

 

[RickSpr]

The recordset is probably not updatable because one of the keys you're using to relate them doesn't have a unique index on either side of the relationship. In such a case, Access assumes the relationship is many-to-many. Queries that contain a many-to-many relationship are not updatable.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[shankbanta]

OK, so can I force a one-to-one relationship?

tables

CPU - PK serial# FK WorkstationName
Montior - PK Serial# FK WorkstationName
Workstation - PK Name

One CPU and one monitor make up a workstation.

Workstation has a unique key of Name
Name is a foreign key in CPU and Monitor.

Join properites are set at All workstations and only CPU and Monitors that where join fields are equal (workstation name).

 

[shankbanta]

Tested the query and forms. Forms will update with cpu and workstation query and also with monitor and workstation query but not all three together.

 

[RickSpr]

Now that you've presented the key information, it's clear that you have one-to-many relationships between Workstation and CPU, and Workstation and Monitor.

If you look in the Help file under "update queries, data that can be updated" (for Access 97; try similar topics in later versions) you'll see a list of conditions that make a query non-updatable, and possible solutions. One of the conditions is "three or more tables comprising a many-to-one-to-many relationship", which is what you have with these three tables. (You didn't explain how the Dept table fits in, so I'm ignoring it here.)

The suggested solution for your case is that you can not update the query directly, but you can update the form based on the query if you set its RecordsetType property to "Dynaset (Inconsistent Updates)".

See the help file for more information.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein