Operation must use an updatable query. (Error 3073)

I get this error when the program is trying to update a table in a query, say Query1. This table in Query1 is joined to another query in the application, Query2 . I suspect it is this second query that is causing the trouble. I have asked my DBA to change the permissions in the tables used in both queries, and she did, but I still get the updatable query error.
Is there anything else I can do?
UcaihcAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jerryb30Commented:
If the queries involve joins which could create any abimguity from a one-0to-many relatiojnship, then your updates would not be allowed.  Quick test-Open query in select mode, and see if you can edit records.  
Cqan you post SQL of queries?
thenelsonCommented:
If you are trying to update the data through a form sourced to the query, you can change the form's dataset type to "Dynaset (Inconsistant Updates) to make the form updatable in some cases.  If that doesn't work, you need to change the query or use Update or Insert queries to update the data.  Type "When can I update data from a query?" in MSAccess help answer wizard for more information on when queries (datasets) are updatable or check this link:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304474#XSLTH4225121123120121120120
UcaihcAuthor Commented:
Here is the SQL for Query2. Then I open this query, I am not able to edit records. Is this the problem? This query is joined to just one table in Query1.

SELECT PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.LAST_NAME,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.FIRST_NAME,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.STATUS_CODE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.STATUS_DATE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.EOD_DATE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.OATH_DATE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PROJECTED_COS_DATE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.ACTUAL_COS_DATE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PRIMARY_LANGUAGE,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.ASSIGNMENT_AREA_NO,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.AA_CNTY_NO,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PROJ_NO,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PCV_COUNTRY,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PCV_PROJECT_NO,
PCVDBMS_PCV_ADDRESS.CITY, PCVDBMS_PCV_ADDRESS.STATE,
PCVDBMS_PCV_ADDRESS.ZIP, PCVDBMS_PROG_NAME.REGION,
PCVDBMS_PCV_ADDRESS.ADDRESS_CODE, PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.SSN,
PCVDBMS_PROG_NAME.FORMAL_NAME, PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.TRAIN_CLASS,
PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.AA_NO
FROM (PCVDBMS_PCV_ADDRESS RIGHT JOIN PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW
ON PCVDBMS_PCV_ADDRESS.VOL_ID = PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.VOL_ID)
LEFT JOIN PCVDBMS_PROG_NAME ON PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.AA_CNTY_NO
= PCVDBMS_PROG_NAME.AA_CNTY_NO
WHERE (((PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PROJECTED_COS_DATE)>Date()-730
Or (PCVDBMS_PCV_VOLUNTEER_PROJECT_VIEW.PROJECTED_COS_DATE) Is Null)
AND ((PCVDBMS_PCV_ADDRESS.ADDRESS_CODE)="HOR"));
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

thenelsonCommented:
Your query meets this definition from the link I gave you:
Query based on three or more tables in which there is a many-to-one-to-many relationship      

This is what the link says About this type of query:
Though you can't update the data in the query directly, you can update the data in a form or data access page based on the query if the form's RecordsetType property is set to Dynaset (Inconsistent Updates).

So if you have a form with its record source is this query, the form may be updateable if its RecordsetType property is set to Dynaset (Inconsistent Updates). Since you state that this query is based on another query, whether or not the form is updatable would depend on the referred to query.  Try setting the form's property to Dynaset (Inconsistent Updates) and see what happens.
UcaihcAuthor Commented:
Hi,

I have been sick and did not check this for a few days.

The link you gave me is not relevant for my case, and the update is a behind the scenes thing, so does not involve any forms. The table that i am trying to update is a simple table joined in a one-to-one relationship with the above query, which is in effect a second table. So, in effect i am working with two tables having a one-to-one relationship.

Whether the above query is updateable shouldn't really matter. I don't see why it does.
Is there anything else I can do?
jerryb30Commented:
If the query2 is not updatable, and is joined to the table, even in a one to one relationship, the results might in fact also not be updateable.  
UcaihcAuthor Commented:
Ok, then is the best way just to do it from code?
jerryb30Commented:
Possibly.  Don't know the complexity of the update, such as source and relationships.  
UcaihcAuthor Commented:
Before I use code is there anything else I can do? Import the query data into a table that will somehow be disconnected from the query so I can use the new table in the update?

Any other workaround?
jerryb30Commented:
can you post sample data, including your 'query1' sql?  it may be as simple as changing join strategy.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
UcaihcAuthor Commented:
Thank you so much. I did change the join and it worked -- the query became 'updateable'. However, the next time I opened the file, the new join no longer worked. Things seem to be jumping around on me. Can you tell me what this sounds like?
jerryb30Commented:
A.   Update created conditions where there were no records matching other criteria.
B.  You didn't save your change to the join. :>)
C.  Undocumented randomizing feature in Access.  :>)
UcaihcAuthor Commented:
What does this mean
jerryb30Commented:
Again, despite being a sage, I cannot infer what the problem is except in general terms.
a.  If you successfully were able to do an update based on an adjustment of the join, then maybe the data has changed, so the join returns no updateable records.
b.  Easy to make changes, see that they work, and not save changes.
c.  Would need to see the specific tables and sql to further analyze.
UcaihcAuthor Commented:
I saved the join changes. Are you saying in part a that if 0 records were updated, then the application would return the error (must use an updateable query)?
jerryb30Commented:
No.  At this point, would really have to see sample of tables structure, all SQL to make sassessment.  Situations in which records are or are not updateale are very tenuous.  
jerryb30Commented:
Did you get the mystery solved?
UcaihcAuthor Commented:
Yes, I sent a comment, but I guess it got lost.

I used the different join as you suggested. I was also careful about checking the primary key box of the tables in production when I re-created the links. That seems to have made the difference.

Thanks for your help
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.