Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Update Query (Access 2002)

    I am trying to write an update query in Access 2002. I am using the query designer and sql designer (not VB). My query is a two step process. First it pulls records into a query, doing calculations on the fields. Then the second query (the update query) updates another table with the results of the first query. Every time I run my update query though, I get "Operation must use an updatable query. (Error 3073)." Everything seems right to me, but I'm at a lost of why I get that error.

    The sql that is being used for the update query is:
    UPDATE Stage_Code_Detail_01_Prev_Year_Audit INNER JOIN (Codes INNER JOIN [Code Detail] ON Codes.ID = [Code Detail].Codes_Id) ON (Stage_Code_Detail_01_Prev_Year_Audit.Member_Id = [Code Detail].Member_Id) AND (Stage_Code_Detail_01_Prev_Year_Audit.CodeID = Codes.CodeID) AND (Stage_Code_Detail_01_Prev_Year_Audit.Expr1 = Codes.[Code Inception Date]) SET [Code Detail].[Estimated Payroll] = Stage_Code_Detail_01_Prev_Year_Audit![Audited Payroll];

    If anybody can offer any assistance, I would be much appreciative. I actually have other queries that are giving me similar errors, but some queries that work right (so I did something right, just can't figure out what)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query (Access 2002)

    If you open the Select query by itself, can you edit data/add new records? If not, an update query based on it will not run.

  3. #3
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query (Access 2002)

    Yes I can.

    I can also change my update query to a regular select query and it runs just fine.

    None of my tables are read only, It is the same tables that I have been doing other update queries to.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Update Query (Access 2002)

    Any you can actually edit/add records when you change the update query to a select query? Strange.

    Does it help if you set the Unique Records property of the update query to Yes? This is equivalent to UPDATE DISTINCTROW instead of just UPDATE in SQL.

  5. #5
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Update Query (Access 2002)

    Check your data types that you are trying to update.

    You said that the query works if you change the update query to a select query. However, you receive the error when the query tries to update the [Code Detail].[Estimated Payroll] field.

    Look at the resultant data set when using the query as a Select query. Check for Null values or determine what type of data is being returned (i.e., text?). Then check the datatype for what you are updating. You should receive an error if the data types are inconsistent.

    You could also try to apply a filter on your update query to if there is known valid data to test against to see if it works.

    HTH
    Regards,

    Gary
    (It's been a while!)

  6. #6
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query (Access 2002)

    Thank you very much for your posts.
    The good news, it appears the issue I had was with Nulls. I didn't know that you couldn't update if your source field had any nulls in it.
    The bad news, out of hopes that that was the solution for everything, I found myself not to be that lucky.

    My next query, which is doing a similar thing with a stage query and then an update query is give me the same error. I have made sure there are no nulls, checked all of the data types, and tried unique records setting.

    Here is the sql for this one:
    UPDATE [Member Detail] INNER JOIN Stage_Mem_Detail_01_Man_Prem_Per_Mem ON ([Member Detail].Member_Id = Stage_Mem_Detail_01_Man_Prem_Per_Mem.Member_Id) AND ([Member Detail].Policy_Id = Stage_Mem_Detail_01_Man_Prem_Per_Mem.Policy_Id) SET [Member Detail].[Estimated Modified Premium] = [Stage_Mem_Detail_01_Man_Prem_Per_Mem]![Expr1], [Member Detail].[Audited Modified Premium] = [Stage_Mem_Detail_01_Man_Prem_Per_Mem]![Expr2];

    Thank you in advance for all your help.

  7. #7
    New Lounger
    Join Date
    May 2004
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Update Query (Access 2002)

    (Edited by HansV to make URL clickable - see <!help=19>Help 19<!/help>)

    FYI: This is nasty, but I think this is the solution to my problem
    http://chrishiller.net/archives/databases/...eable_query.php

    Thanks again everybody for your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •