Page 1 of 3 123 LastLast
Results 1 to 15 of 34
  1. #1
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408

    query not updatable

    hi,
    i use this query to update a table

    Code:
    "UPDATE [LK contatti_messaggi_totaliNOAGG] INNER JOIN [LK contatti_messaggi TOT UNICI LOCALE] ON  ([LK contatti_messaggi_totaliNOAGG].[Nome cognome] = [LK contatti_messaggi TOT UNICI LOCALE].[Nome cognome]) AND (([LK contatti_messaggi_totaliNOAGG].Company = [LK contatti_messaggi TOT UNICI LOCALE].Company) XOR ([LK contatti_messaggi_totaliNOAGG].Position = [LK contatti_messaggi TOT UNICI LOCALE].Position)) SET [LK contatti_messaggi TOT UNICI LOCALE].[Position] = [LK contatti_messaggi_totaliNOAGG].[Position], [LK contatti_messaggi TOT UNICI LOCALE].Company = [LK contatti_messaggi_totaliNOAGG].[Company], [LK contatti_messaggi TOT UNICI LOCALE].[Data Invio] = IIf([LK contatti_messaggi_totaliNOAGG].[data invio]>[LK contatti_messaggi TOT UNICI LOCALE].[data invio],[LK contatti_messaggi_totaliNOAGG].[data invio],[LK contatti_messaggi TOT UNICI LOCALE].[data invio]);"
    LK contatti_messaggi_totaliNOAGG is a query that replicates values from an aggregation query with no calculation
    LK contatti_messaggi TOT UNICI LOCALE is the table i want to update.

    i get the error "for the operation you need an updatable query"



    it's 3 hours i'm trying to understand but with no results.
    help

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,861

  3. #3
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    thanks,
    however i had already a problem like that and i made another "bridge" query based on aggregation query and it solved the problem. now it is not.
    what's your suggestion? maybe i have to make a support table?

  4. #4
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    I made a "create table" query and used this table instead. works nice

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    If you have a read only query used as part of another query, that will also be read only. Ideally, you should fix the cause of your read only query/queries.

    Make table queries are usually not the best solution to this issue as they cause database bloat. In addition if you do this repeatedly it may lead to instability.
    A better alternative would be to use a 'temp' table and append data to that then empty it after use. Even so, your database will still get much larger
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    i just figured out that all those queries feeds on 4 linked table. is this the problem, right?

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    The more tables that are used in a query, the greater the chance of it being read only.
    If you are able to use fewer than 4 tables, that may solve the issue.
    The fact that the tables are linked isn't relevant UNLESS one or more tables are linked Excel spreadsheets - those can no longer (since 2002!) be updated from Access
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    Yes, the table are linked to excel. However the source is csv, maybe I can use it as a text document instead of an excel file

  9. #9
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Sadly that won't work either.
    If you try & edit a linked Excel or csv file, you will get a message like this:
    Click image for larger version. 

Name:	Capture.PNG 
Views:	28 
Size:	4.9 KB 
ID:	43261
    This means any query based on those will be read only.
    You MAY be able to add a record but once you have done it can't be edited or deleted!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  10. #10
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    Wait a moment, maybe I explained it very bad for my bad English. My query updates a local table getting values from the linked tables

    EDIT there are two union query in the process. Maybe i'll use a tab in which i append my data, the calculation are very complex
    thanks
    Last edited by diegomarino; 10-20-2020 at 07:50 AM.

  11. #11
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Did you look at the link provided in post #2? This is what it says:

    Why is my query read-only?

    If you cannot edit the data in a query, this list may help you identify why it is not updatable:

    • It has a GROUP BY clause. A Totals query is always read-only.
    • It has a TRANSFORM clause. A Crosstab query is always read-only.
    • It uses First(), Sum(), Max(), Count(), etc. in the SELECT clause. Queries that aggregate records are read-only.
    • It contains a DISTINCT predicate. Set Unique Values to No in the query's Properties.
    • It involves a UNION. Union queries are always read-only.
    • It has a subquery in the SELECT clause. Uncheck the Show box under your subquery, or use a domain aggregation function instead.
    • It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.
    • The fields in a JOIN are not indexed correctly: there is no primary key or unique index on the JOINed fields.
    • The query's Recordset Type property is Snapshot. Set Recordset Type to "Dynaset" in the query's Properties.
    • The query is based on another query that is read-only (stacked query.)
    • Your permissions are read-only (Access security.)
    • The database is opened read-only, or the file attributes are read-only, or the database is on read-only media (e.g. CD-ROM, network drive without write privileges.)
    • The query calls a VBA function, but the database is not in a trusted location so the code cannot run. (See the yellow box at the top of this Access 2007 page.)
    • The fields that the query outputs are Calcluated fields (Access 2010.)
    Can you see why its read only now?
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  12. #12
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    yes sure, the real problem was to remember all the passages i made. unfortunely i need that union queries. thanks

  13. #13
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Well, maybe you do need it, in which case there is no solution other than a make table query.

    However, too often union queries are used to deal with table design issues where data is in two or more separate tables but should be in one table with a suitable identifying field such as Category. If that is feasible, then no union query is required

    Anyway, good luck with your project
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  14. #14
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    The table are contacts exported from my two LinkedIn accounts, I have to merge them without duplicated values. So i think it's the only way to elaborate.
    However all the stuff now works, I can manage my LinkedIn data as I want.
    Thanks

  15. #15
    diegomarino is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Feb 2018
    Posts
    408
    you know what? linkedin just changed the data exportation, so i have to do everything from scratch.
    a least, now the data are more interesting

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Updatable query
    By Syla in forum Queries
    Replies: 3
    Last Post: 03-25-2020, 10:22 AM
  2. Non-Updatable Query Problem
    By RMittelman in forum Programming
    Replies: 4
    Last Post: 11-22-2019, 09:13 AM
  3. Updatable query in Access 2010
    By haydenbl in forum Queries
    Replies: 12
    Last Post: 11-05-2013, 06:59 PM
  4. Updatable query
    By Starscream in forum Queries
    Replies: 3
    Last Post: 04-26-2013, 07:59 AM
  5. Operation must be an updatable query
    By Lorlai in forum Queries
    Replies: 5
    Last Post: 02-02-2012, 02:04 PM

Posting Permissions

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