Operation must use and updatable query. (Error 3073)

Operation must use and updatable query. (Error 3073) Can somone tell me why I get this error in Debug when I run an update. it comes to the line DoCmd.OOperation must use and updatable query. (Error 3073) Any assistance is greatly appreciated.  I'm a novice please help.
 

Question Info


Last updated September 18, 2018 Views 862 Applies to:

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?

 

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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. 

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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.

 

John W. Vinson/MVP

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

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

Did this solve your problem?

Sorry this didn't help.

Great! Thanks for marking this as the answer.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this response?

Thanks for your feedback.