[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7670
  • Last Modified:

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?
0
Ucaihc
Asked:
Ucaihc
  • 8
  • 8
  • 2
1 Solution
 
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?
0
 
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
0
 
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"));
0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
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.
0
 
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?
0
 
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.  
0
 
UcaihcAuthor Commented:
Ok, then is the best way just to do it from code?
0
 
jerryb30Commented:
Possibly.  Don't know the complexity of the update, such as source and relationships.  
0
 
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?
0
 
jerryb30Commented:
can you post sample data, including your 'query1' sql?  it may be as simple as changing join strategy.
0
 
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?
0
 
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.  :>)
0
 
UcaihcAuthor Commented:
What does this mean
0
 
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.
0
 
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)?
0
 
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.  
0
 
jerryb30Commented:
Did you get the mystery solved?
0
 
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
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 8
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now