Why can't I update this query

[BruceM]

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for line
items on the PO, which serves as the junction table between the Vendor table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation, as
tblPO is related to tblPO_Details, which as I mentioned is a junction table.
The article is ambiguous in that it doesn't specify whether the problem lies
with a query that includes the three table involved in the many-to-many, or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.

 

[Marshall Barton]

This is the SQL for a query that cannot be updated:

SELECT tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
FROM (tblVendor INNER JOIN tblPO ON tblVendor.VendorID = tblPO.VendorID)
INNER JOIN tblPhone ON tblVendor.VendorID = tblPhone.VendorID
WHERE (((tblPhone.MainPhone)=True));

The situation, in general terms, is that I have a pretty standard PO system.
There is a Vendor table, with a related Purchase Order (PO) table. If it
matters for purposes of this question, there is a PO_Details table for line
items on the PO, which serves as the junction table between the Vendor table
and a Products table.
The Vendor table also has a related table for phone numbers. Therein lies
my problem. The PO form needs to contain Vendor information including the
phone number, and PO information such as PO_Date and PO_Number. As long as
I leave out the Phone table, everything is fine.
If I make a query consisting of tblVendor and either tblPhone or tblPO,
everything is fine; however, adding both tblPhone and tblPO locks down the
query for reasons I have not been able to discover. I do know that changing
the join type has accomplished nothing. An article in Microsoft's MSDN2
library contains the following as a reason for a non-updatable query:
"Query based on three or more tables in which there is a many-to-many
relationship"
This is the closest I can find to something that applies to my situation, as
tblPO is related to tblPO_Details, which as I mentioned is a junction table.
The article is ambiguous in that it doesn't specify whether the problem lies
with a query that includes the three table involved in the many-to-many, or
just any query with three or more tables, one of which is part of a
many-to-many relationship. If it applies to my difficulties I do not see
the solution.

Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better. The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

 

[Lynn Trapp]

To add to Marsh's excellent comments, if you think about it logically, how
will Access know which table to update? It's simply too complex to make a
decision and, thus, it leaves it in a non-updatable status.

 

[BruceM]

Well, the first thing is that you should not be treating a
multi-table dataset as a data editing mechanism. I think(?)
many other db systems just plain disallow it. Access/Jet
will sometimes permit it in an effort (unjustified in my
opinion) to make things easier for people that don't know
any better.

I never intended to edit the phone number from the PO form, only to display
it. The display-only fields were to be in locked controls. Do I understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.

The only hope you have of making this
arrangement work is to include each table's primary key in
the dataset.

Tried that. It didn't work.

The "proper" way to deal with updating related information
in multiple tables is to use a form for the table of primary
interest and a separate subform for each related table's
data.

I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.

 

[BruceM]

Thanks for the reply. If I was able to see the logic I would have had no
need to ask the question, but it eludes me. I identified fields as being
from specific tables, so I don't see the ambiguity. In answer to your
question, when there is only one field of a particular name in the dataset I
don't see why Access has a problem with identifying it. However, I doubt
it's a matter of using unique field names, as I suspect the situation would
have been exactly the same even if VendorID had a different name in all
three tables. I would like to understand, but can't quite grasp it.
By the way, the user-level security is working nicely (albeit in a test
environment for now), thanks to your assistance in an earlier thread. I
still need to work out using a form to check for the password, as you
described, but in that case I do see the logic based on your explanation, so
I expect it will work OK.

 

[Lynn Trapp]

Well, basically, try running the following update statement and see what
error you get.


Update tblPO.*, tblVendor.*, tblPhone.Phone, tblPhone.MainPhone
Set tblPhone.Phone = "111-111-1111"
Where tblPhone.MainPhone = True;

I believe the error will be something to the effect that you need to specify
the table name for update.

 

[Marshall Barton]

I never intended to edit the phone number from the PO form, only to display
it. The display-only fields were to be in locked controls. Do I understand
you to mean a forms's record source should be either a table or a
single-table query? Would it be better to have a subform for the vendor
information such as address (and phone) that is displayed only? The
editable fields could be on the main (PO) form, and the displayed-only
fields on the subform, which could be locked to prevent edits. I have to
say I always thought multi-table Record Source queries were a strength of
Access/Jet, not a liability.


Tried that. It didn't work.
I just wish there was a convenient way of handling one-field subforms so
that they look like text boxes. There's lots of trial and error, and in the
end a control that doesn't quite look like the rest of the controls.

Anyhow, the subform solves the immediate problem, of course, even if it is a
fiddly nuisance to work with. I will admit, though, to being moderately
confused about datasets. Thanks for taking the time to look at my
questions, as you have to very good effect on a number of past occasions.

I thought you were trying to modify the phone field. Other
than that issue, I think I just added to your confusion.
I have more form record source queries than I can count that
use multiple tables and as long as only the primary table's
fields are being updated, there should be no problem. The
problem I was trying to address is in editing a many side
table's fields.

Just guessing now, but maybe something else going on. Can
you find anything unusual about the phones table? Try
joining that table just to the vendor table and see if the
vendor data can be modified.

 

[BruceM]

Thanks for the reply. I guess I didn't make it clear that I was just trying
to display, not edit, the vendor data. I'll have a command button or a
click event for the controls bound to the vendor data if the user needs to
go to the main vendor form, in which the phone numbers are displayed in a
list box. Phone numbers are added/edited by means of a pop-up form.
However, only a few users will be able to edit vendor data at all, so most
of the front ends will include just a message box to inform users they can't
change the data.
A query that combines tblVendor and tblPhone can be edited, as can a query
that combines tblPO and tblVendor. FWIW, the only indexed fields are the PK
and FK fields, and VendorName from tblVendor. It makes no difference if I
add fields individually or by using the asterisk to add all fields, nor if I
remove the parameter. I tried breaking the relationship between tblPO and
tblPO_Details (which is the junction table between tblPO and tblProducts) so
that a many-to-many relationship is completely out of the mix.
In order to get the phone numbers, which were in Phone1 and Phone2 fields in
tblVendor, into their own table I based an append query on a union query. I
wondered if this could have created some anomalies, so I create a new
tblPhone with no data. When I added tblPO, tblVendor, and the new tblPhone
to a query it returned no records because tblPhone had none, so I changed
the join between tblVendor and tblPhone to a left join, after which I could
see all of the PO records, but I still couldn't edit any fields. This tells
me that when I prepare the report that is the actual printed PO I can
include all three tables in its record source, but need to use the left join
in order to display a record regardless of whether there is a phone number.
However, this doesn't get me any closer to displaying the phone number in a
form based on an updatable query, unless I use a subform.
If a query that includes all three tables is supposed to be non-updatable I
can accept that, but if I should be able to edit the PO fields but cannot I
would like to discover why, and thereby learn something new.
I may end up using a subform to display the vendor information, including
phone, since it will be simpler to prevent edits or additions in the subform
than in individual controls, but if you have any thoughts or explanations
about why I can't edit any of the fields when all three tables are in the
query, I would be interested in hearing about it.

 

[BruceM]

Thanks for the reply. I tried the experiment, and was unable to update when
I used the asterisks (the error message was atypically clear on that point),
but when I added individual fields instead, the update was successful for
those vendors who had a PO record (a PO had been created to buy something
from the vendor).
However, as I mentioned to Marshall, adding individual fields to the query
still resulted in a non-updatable query.
As I also mentioned to Marshall, I have no intention of modifying the phone
number or any other vendor information from the PO form. My only goal is to
display the vendor information (in locked text boxes). When I think about
it, there is probably no compelling reason to have that information (other
than VendorName, which appears on the PO form as the visible column in a
combo box bound to VendorID in tblPO) appear on the form, as long as it
shows up on the report. However, I would still like to solve the puzzle, or
if it is expected behavior I would like to understand it.

 

[Marshall Barton]

I can't explain it. Barring something funny about your
query, it should work. How you created the table and the
relationships should have no effect as long as the query is
properly constructed, which it looks like it is.

Someone else may have an idea, but the only straw I can
think of to grasp at is some kind of corruption. Make a
backup copy of the mdb file, then try compacting the db,
recreating the query from scratch or even creating a fresh
mdb and importing everything.

 

[BruceM]

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and creating a
query
Creating a new database, creating new tables, and adding some test data

In all cases, the query would not allow data to be updated in any way. Here
is the basic structure of the test database. By the way, I tried using
unique names for the FK fields, but the result was the same as if I had used
VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to be as
they should. Changing the join type in the query makes no difference. The
query cannot be edited. I can only conclude this is designed behavior. I
just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the information
in this posting just in case something jumps out at you, but this may have
to remain a mystery.

I can't explain it. Barring something funny about your
query, it should work. How you created the table and the
relationships should have no effect as long as the query is
properly constructed, which it looks like it is.

Someone else may have an idea, but the only straw I can
think of to grasp at is some kind of corruption. Make a
backup copy of the mdb file, then try compacting the db,
recreating the query from scratch or even creating a fresh
mdb and importing everything.
--
Marsh
MVP [MS Access]

Thanks for the reply. I guess I didn't make it clear that I was just
trying
to display, not edit, the vendor data. I'll have a command button or a
click event for the controls bound to the vendor data if the user needs to
go to the main vendor form, in which the phone numbers are displayed in a
list box. Phone numbers are added/edited by means of a pop-up form.
However, only a few users will be able to edit vendor data at all, so most
of the front ends will include just a message box to inform users they
can't
change the data.
A query that combines tblVendor and tblPhone can be edited, as can a query
that combines tblPO and tblVendor. FWIW, the only indexed fields are the
PK
and FK fields, and VendorName from tblVendor. It makes no difference if I
add fields individually or by using the asterisk to add all fields, nor if
I
remove the parameter. I tried breaking the relationship between tblPO and
tblPO_Details (which is the junction table between tblPO and tblProducts)
so
that a many-to-many relationship is completely out of the mix.
In order to get the phone numbers, which were in Phone1 and Phone2 fields
in
tblVendor, into their own table I based an append query on a union query.
I
wondered if this could have created some anomalies, so I create a new
tblPhone with no data. When I added tblPO, tblVendor, and the new
tblPhone
to a query it returned no records because tblPhone had none, so I changed
the join between tblVendor and tblPhone to a left join, after which I
could
see all of the PO records, but I still couldn't edit any fields. This
tells
me that when I prepare the report that is the actual printed PO I can
include all three tables in its record source, but need to use the left
join
in order to display a record regardless of whether there is a phone
number.
However, this doesn't get me any closer to displaying the phone number in
a
form based on an updatable query, unless I use a subform.
If a query that includes all three tables is supposed to be non-updatable
I
can accept that, but if I should be able to edit the PO fields but cannot
I
would like to discover why, and thereby learn something new.
I may end up using a subform to display the vendor information, including
phone, since it will be simpler to prevent edits or additions in the
subform
than in individual controls, but if you have any thoughts or explanations
about why I can't edit any of the fields when all three tables are in the
query, I would be interested in hearing about it.

"Marshall Barton" wrote.

 

[Rick Brandt]

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and
creating a query
Creating a new database, creating new tables, and adding some test
data
In all cases, the query would not allow data to be updated in any
way. Here is the basic structure of the test database. By the way,
I tried using unique names for the FK fields, but the result was the
same as if I had used VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to
be as they should. Changing the join type in the query makes no
difference. The query cannot be edited. I can only conclude this is
designed behavior. I just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the
information in this posting just in case something jumps out at you,
but this may have to remain a mystery.

I don't know that the "rules" for when query is updateable are really that
well-defined for the case of queries that include multiple tables. It might
very well be influenced by the query plan that is compiled and saved for the
query which can varies based on indexes available and by the actual data in
the tables.

It should be pointed out that editable record sets from ANY query having
multiple table inputs is not allowed in most databases. Access is one of
the most flexible in this regard, but (as observed) there are limits to how
complex a query's joins can be before *safely* allowing edits can be
executed and as a safeguard the result set becomes read only when any
ambiguity creeps in.

In all my years of using Access I have only a small handful of cases where I
have attempted to edit data from a multi-input query. It is just not that
necessary of a thing to do.

 

[BruceM]

Thanks for the reply. I am not attempting to add or edit data other than
Purchase Order information. Further, I have no wish to do so. VendorID is
the FK field in tblPO, so the combo box on frmPO has been made to display
the vendor name, but the rest of the vendor information is for display only.
Since there were only to be three such fields (a concatenated address field,
fax, and phone) I thought it would be simple enough to just lock those three
text boxes and not bother with a subform, but it is not possible. I believe
the limitation in my case is that two tables were linked to the same field
in the third table. Both tblPhone and tblPO have FK fields related to the
same PK field (VendorID) from tblVendor. The query I described is not
updatable. However, a query combining tblVendor, tblPO, and tblPO_Details
is editable. Each vendor may have many POs, and each PO may have many line
items. This is a different situation than I have described, in which each
vendor may have many POs, and each vendor may have many phone numbers.
Once again, I do not intend, in this project or any other, to edit fields
from more than one table that is included in a query, unless some
exceptional circumstances present themselves in the future. There are times
when it would be convenient if I did not have to use a subform, particularly
when I only need to display a single field, but now that I have a better
handle on the limitations of multi-table queries as form record sources I
can move on to other things. In the present case I constructed a locked
subform that combines tblVendor and tblPhone.

 

[Rick Brandt]

Thanks for the reply. I am not attempting to add or edit data other
than Purchase Order information. Further, I have no wish to do so.
VendorID is the FK field in tblPO, so the combo box on frmPO has been
made to display the vendor name, but the rest of the vendor
information is for display only. [snip]

I have had queries where linking to one additional table so that one field
could be added for viewing only made that query non-editable. Which fields
you intend to edit or not edit doesn't enter into the equation.

 

[Marshall Barton]

I have tried:
Creating a new query
Compacting and repairing the database
Creating a new blank database, and importing all of the objects
Creating a new blank database, importing only the tables, and creating a
query
Creating a new database, creating new tables, and adding some test data

In all cases, the query would not allow data to be updated in any way. Here
is the basic structure of the test database. By the way, I tried using
unique names for the FK fields, but the result was the same as if I had used
VendorID in all cases.

tblVendor
VendorID (PK)
VendorName, etc.

tblPO
PO_ID (PK)
VendorID_PO (FK)
PurchaseDate, etc.

tblPhone
PhoneID (PK)
VendorID_phone (FK)
PhoneNumber

The relationships have referential integrity enforced, and appear to be as
they should. Changing the join type in the query makes no difference. The
query cannot be edited. I can only conclude this is designed behavior. I
just wish I knew why.

SELECT tblPO.*, tblVendor.*, tblPhone.Phone
FROM (tblVendor INNER JOIN tblPhone ON tblVendor.VendorID =
tblPhone.VendorID_phone) INNER JOIN tblPO ON tblVendor.VendorID =
tblPO.VendorID_PO;


Anyhow, thanks for taking a look at this. I have provided the information
in this posting just in case something jumps out at you, but this may have
to remain a mystery.

Ok, I just spent the last hour duplicating your arrangement
and experiments and I am seeing the same things you are.
All the playing around with it that I've done did not help
any more than what you've been saying all along.

I also went into several of my apps looking for examples
where I've done similar things and, regardless of my
(apparently failing) memory, I can't find any examples where
I actually try to use a three table query for updating
anything. I guess I was off base before when I said I had
done this before.

I did find lots of places where I used a dependent combo
box, a subform or, ocassionally a text box with a DLookup to
display data from a third table.

 

[BruceM]

Yup, I see that it's all or nothing with updatable-ness.

Thanks for the reply. I am not attempting to add or edit data other
than Purchase Order information. Further, I have no wish to do so.
VendorID is the FK field in tblPO, so the combo box on frmPO has been
made to display the vendor name, but the rest of the vendor
information is for display only. [snip]

I have had queries where linking to one additional table so that one field
could be added for viewing only made that query non-editable. Which
fields you intend to edit or not edit doesn't enter into the equation.

 

[BruceM]

Ah! A text box or something with a DLookup, as I did with the list box for
displaying phone numbers (from the related table) in my Vendors form. I
didn't make the connection that I could probably have done the same sort of
thing. I use lots of subforms, but they can be awkward to work with in that
I can't see how they line up until I switch to Form view. If it's not right
I switch back, tweak, and repeat. As I understand this has been improved in
Access 2007, but I won't be seeing that anytime soon, at least not at work.
Anyhow, as I think I mentioned I ended up using a subform for vendor
information, which did have the advantage of allowing me to disallow changes
in one shot at the form level.
As I mentioned to Rick, I realized that in the situation where each vendor
may have many Purchase Orders, and each PO may have many line items, the
query is updatable. However, in my case each vendor could have many POs,
and each vendor could have many phone numbers. Both tblPO and tblPhone were
trying to "share" the same PK from tblVendor. I don't quite understand the
rule that applies, but I see how it works.
Marshall, thanks for taking the time to look into this. Even after I
decided the subform as described was a good way to go about things, I
stubbornly continued in my efforts to discover what was going on. Perhaps
it wasn't the best use of my time in the short term, but I expect the
hard-earned information I gleaned will stay with me pretty well. Thanks for
sticking with me through the investigation.

 

[Marshall Barton]

Ah! A text box or something with a DLookup, as I did with the list box for
displaying phone numbers (from the related table) in my Vendors form. I
didn't make the connection that I could probably have done the same sort of
thing. I use lots of subforms, but they can be awkward to work with in that
I can't see how they line up until I switch to Form view. If it's not right
I switch back, tweak, and repeat. As I understand this has been improved in
Access 2007, but I won't be seeing that anytime soon, at least not at work.
Anyhow, as I think I mentioned I ended up using a subform for vendor
information, which did have the advantage of allowing me to disallow changes
in one shot at the form level.
As I mentioned to Rick, I realized that in the situation where each vendor
may have many Purchase Orders, and each PO may have many line items, the
query is updatable. However, in my case each vendor could have many POs,
and each vendor could have many phone numbers. Both tblPO and tblPhone were
trying to "share" the same PK from tblVendor. I don't quite understand the
rule that applies, but I see how it works.
Marshall, thanks for taking the time to look into this. Even after I
decided the subform as described was a good way to go about things, I
stubbornly continued in my efforts to discover what was going on. Perhaps
it wasn't the best use of my time in the short term, but I expect the
hard-earned information I gleaned will stay with me pretty well. Thanks for
sticking with me through the investigation.

You're welcome, even though I was at least part of the
inefficient use of your time.

 

[BruceM]

I learned something from all of this. It more than makes up for the brief
time I spent heading down the wrong road. Also, you confirmed that the
problem was not some undiscovered fault in my technique. The problem was
that my query could not work, not that I was constructing it incorrectly.
You could have just shrugged and walked away, but you took the time to
investigate, which I appreciate.