Results 1 to 5 of 5
  1. #1
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    56

    Non-Updatable Query Problem

    I have a database to handle donations to a non-profit. There's a table for Thank-You letters. Here is an abbreviated field list:



    DonationID (Long)
    Name (Short Text)
    Address (Short Text)
    Amount (Currency)
    NonCashItems (Long Text)
    Paragraph1 (Long Text)
    Paragraph2 (Long Text)

    Currently, I clear the table, then fill it with everything except Paragraph1 and Paragraph2 using an append query. This is working fine.

    The problem occurs when I try to fill paragraphs 1 & 2 using an update query, getting the data from another table. I want to do this in 2 passes (first append then update) because getting the paragraph data is complex, depending on the type of Thank-You letter.

    When I run an update query to fill in the last 2 fields, it gives me an error about not being an updatable query. This happens whether I use a left-join query to get my paragraph data or use a (sub-query) in the "Update To" cell to get my data.

    If, for testing purposes, I simply update the 2 fields to "Test1" and "Test2", it works just fine.

    Why can I do the append query and it works fine, but when I do the update query I get the updatable query error?

    Thanks...

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,682
    What are you updating from? A query? Is that query editable - can you edit data in that query? If not, this will not allow an UPDATE action.

    Also, if you aren't already aware of this bug, review https://support.office.com/en-us/art...3-f21636caedec
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    isladogs's Avatar
    isladogs is offline Very idle programmer
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    3,991
    There are many reasons why queries may be non updateable
    For a fairly comprehensive list of reasons, see http://allenbrowne.com/ser-61.html
    Colin (Mendip Data Systems) : Website, email
    If this has helped, please click the star button and leave a comment

  4. #4
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    56
    Quote Originally Posted by June7 View Post
    What are you updating from? A query? Is that query editable - can you edit data in that query? If not, this will not allow an UPDATE action.

    Also, if you aren't already aware of this bug, review https://support.office.com/en-us/art...3-f21636caedec
    The query contains a table and another query which is linked to the table via a key field. I'm trying to update a field in the table to a value from the query. Again, if I update the table field with a constant expression such as "Test", it works fine, so I don't understand why the query/table is non-updatable when I use a join to a query value as the update value. Thanks for the reply...

  5. #5
    RMittelman is offline Advanced
    Windows 10 Access 2016
    Join Date
    Dec 2010
    Location
    Simi Valley, CA
    Posts
    56
    Quote Originally Posted by isladogs View Post
    There are many reasons why queries may be non updateable
    For a fairly comprehensive list of reasons, see http://allenbrowne.com/ser-61.html
    Thanks for supplying the list of reasons why query may be non-updatable. Not sure which one applies. The fact that without removing the join from the table to the query, if I simply hard-code a constant as the "update to" value it then works, is confusing. But I've found another way to update my table than an update query, so I guess it's moot now. Thanks again for the reply...

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updatable query in Access 2010
    By haydenbl in forum Queries
    Replies: 12
    Last Post: 11-05-2013, 06:59 PM
  2. Updatable query
    By Starscream in forum Queries
    Replies: 3
    Last Post: 04-26-2013, 07:59 AM
  3. Operation must use an updatable query-- error
    By bhushan98 in forum Queries
    Replies: 2
    Last Post: 12-01-2012, 11:32 AM
  4. Operation must be an updatable query
    By Lorlai in forum Queries
    Replies: 5
    Last Post: 02-02-2012, 02:04 PM
  5. Replies: 1
    Last Post: 12-12-2009, 10:47 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums