April 9, 2024
Contribute to the Microsoft 365 and Office forum!
Click here to learn more 💡
May 10, 2024
Access Forum Top Contributors:
Operation must use and updatable query. (Error 3073)
Report abuse
Thank you.
Reported content has been submitted
You're getting the error because the query you're trying to run is not updateable. There are many reasons this might be the case.
You didn't post your code nor did you post any information about the query (such as its SQL view) so it's more than a bit difficult to tell you how to fix the query. Care to post the code, and (even more useful) the SQL view of the query that you're trying to run?
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
John,
Thanks so much for your reply. I will show you what is written. Just to let you know I was trying to run an updat file and these are the results. Also, I don't have any VB training just trying to figure this out. This is VB inside of Access 07. There was two steps in the query it past the first step and debug stopped at the second step. Below are the two steps:
Private Sub btnCreateNew_Click()
'Suppress warning messages
DoCmd.SetWarnings False
DoCmd.OpenQuery"Step1-CreatNewList",acViewNormal,acEdit
'Turn warning message back on
DoCmd.SetWarning True
End Sub
Private Sub btnAppend_Click()
'Suppress warning messages
Do.Cmd.SetWarnings False
DoCmd.OpenQuery"Step2-AppendNewToCurrent",acViewNormal,acEdit
'Turn warning message back on
DoCmd.SetWarnings True
End Sub
Hope this helps and you can help me. THANKS once again.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Well, suppressing the warning messages prevents Access from telling you what's wrong. For starters, comment out the DoCmd.SetWarnings False lines and see what happens.
Secondly, please open the two queries in design view and select SQL from the "View" dropdown at the left of the Design ribbon. Copy and paste the SQL text to a message here. While you have it open, try running the query using the red exclamation point Run button - it may explain why your code is failing.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
John I thanks you so much for replying. As I mentioned I don't know didly about queries or SQL. I'm reading up on it now to understand. I've been put in a place where the person that built the database was in the military and left and noone to replace him that is able to understand the database so I'm trying to figure this out with no knowledge. So I really do appreciate your help. Listed below is what is in Step-1 and Step-2:
Step1-Create New List
SELECT ImportNGA_GlobalHoldings.* INTO NEWNGA_GlobalHoldings
FROM ImportNGA_GlobalHoldings LEFT JOIN CurrentNGA_GlobalHoldings ON (ImportNGA_GlobalHoldings.EDITION=CurrentNGA_GlobalHoldings.EDITION) AND (ImportNGA_GlobalHoldings.STOCK_NUM=CurrentNGA_GlobalHoldings.STOCK_NUM)
WHERE(((CurrentNGA_GlobalHoldings.STOCK_NULL) Is Null));
Step2-AppendNewToCurrent
INSERT INTO CurrentNGA_GlobalHoldings
SELECT NewNGA_GlobalHoldings.*
FROM NewNGA_GlobalHoldings;
Once again thanks for your time and help.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
John,
Tried to run the query Step2 with the window in SQL view and it said The existing table 'NewNGA_GlobalHoldings' will be deleted before you run the query. Do you want to continue anyway? I said no becasue I didn't know what to do.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Are you certain that the error was on Step2? Because it's Step1 that creates NewNGA_GlobalHoldings, and it will cause an error if that table already exists! The error makes sense, but only if it's occuring at Step1, not at Step2.
Could you step back a bit and describe the process? Why are you creating a completely new table (which will require deleting the old table) AT ALL, rather than just appending directly from ImportNGA_GlobalHoldings into CurrentNGA_GlobalHoldings? You have two CURRENT tables mentioned - Current_GlobalHoldings and Current_NGAGlobalHoldings; which is which, and which one do you want to update?
INSERT INTO Current_GlobalHoldings
SELECT ImportNGA_GlobalHoldings.*
FROM ImportNGA_GlobalHoldings LEFT JOIN CurrentNGA_GlobalHoldings ON (ImportNGA_GlobalHoldings.EDITION=CurrentNGA_GlobalHoldings.EDITION) AND (ImportNGA_GlobalHoldings.STOCK_NUM=CurrentNGA_GlobalHoldings.STOCK_NUM)
WHERE(((CurrentNGA_GlobalHoldings.STOCK_NUM) Is Null));
This will find those records in the IMPORT table for which there is no matching record in the CurrentNGA_GlobalHoldings table on STOCK_NUM and EDITION, and insert them into the Current_GlobalHoldings table in one pass. If there is some other reason for the two-step process post back and explain.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Yes the error is on Step2. When I try to run the update file i get the debug error that states "operation must use and ubdatable query (3073)" then it highlights in yellow the Step2 DoCmd. What I actully do is get an up to listing from NGA which is imported into the database that was created. After that I just hit update and the rocess runs ok unless it hits a glitch like this. Previously we changed the path and had to remap things in the link manager to the new path. It worked ok. Now this era comes up. Thanks again for all of your assistance.
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
I'm sorry, but this simply isn't making any sense to me!
The query you posted as Query2 is NOT AN UPDATE QUERY. I don't see how it could generate this error.
When you click btnCreateNew do you get a new table named NEWNGA_GlobalHoldings?
Does it contain what you expect?
When you click btnAppend you get the error message?
What do you mean by "Step 2 DoCmd"? There is no such step in your code.
Report abuse
Thank you.
Reported content has been submitted
1 person found this reply helpful
·Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Im sorry this is coming across so messed up. i wish I could talk to you via landline if at all possible. Remember I dont know anything about VB or SQL. All I know is you write a syntax to make thing do what you want. John to answer your questions don't know if there is a new table name NewNGA_Globaholdings what i think happens is when you get a new update to import into the existing database that is considered the new table, I think only the new information is extracted from the the new updateand that is why append iis used.
What I meant by Step2 is the Append I was just talking about it starting with the Docmd thats all. I hope I have not confused you more. Please let me know if we can talk via l/l if so I will call you instead of you spending money. As always thanks for trying to help me.
Novice
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
John,
Ijust went into the update file. Iclicked on design view in forms na I ended up gett the same error when i clicked in formview. Would that mean anything to you. Just asking, trying to figure this out.
Novice
Report abuse
Thank you.
Reported content has been submitted
Was this reply helpful?
Sorry this didn't help.
Great! Thanks for your feedback.
How satisfied are you with this reply?
Thanks for your feedback, it helps us improve the site.
How satisfied are you with this reply?
Thanks for your feedback.
Question Info
Last updated June 28, 2023 Views 3,890 Applies to: