Results 1 to 8 of 8
  1. #1
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74

    Query not updateable

    Can anyone tell me why this query based on two local tables isn't editable? I made sure the fields were the same data type, indexed, etc. and I can't think of one reason this isn't updateable, but I really need to use the rs.edit method in my code on this recordset. I tried to assign the relationship in the relationships area first, but it listed the relationship as "indeterminate". It should be a many to many relationship. For example, the Data table has one record for each line item for each month for each calculation type, whereas the formula component table has multiple records per line item per calc type (but does not have a different record for each month).



    Click image for larger version. 

Name:	join.JPG 
Views:	19 
Size:	47.1 KB 
ID:	37409

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Try setting Unique Records = Yes .... or in SQL view that's SELECT DISTINCTROW

    See also http://allenbrowne.com/ser-61.html for info on reasons why queries become read only
    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

  3. #3
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Thanks - that didn't work but I think it pointed me in the right direction. I tried changing unique values, unique records, etc. and nothing worked, but then I saw the options for recordsettype and "Dynaset (Inconsistent Updates)" was an option. I tried it and it works, but anything with the word "inconsistent" scares me. I researched it a bit but didn't find much info, but it does seem like it's intended for a scenario like this.

    Has anyone worked with Dynaset (Inconsistent Updates)? Any tips?

  4. #4
    A S MANN is offline Advanced System Analyst
    Windows XP Access 2007
    Join Date
    Oct 2010
    Location
    India
    Posts
    161
    The Table are linked by two relationship which make it non updateble. Keep only one relationship which is unique and than try again.

  5. #5
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    I got it working - the syntax was a bit off. For anyone else wondering, the way to edit these records in a recordset is:

    Set rs = db.OpenRecordset("Select * Table;", dbOpenDynaset, dbInconsistent)

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    You should be very careful about using dbInconsistent as it will allow updates to all fields even if other records are affected in doing so.
    So whilst it often solves issues with read only recordset, it may have unpredictable results that you won't want.

    A better solution would be to redesign your record source so it id editable and any changes are then predictable
    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

  7. #7
    mcucino is offline Advanced Beginner
    Windows 8 Access 2016
    Join Date
    Jul 2017
    Location
    Providence, RI
    Posts
    74
    Can you think of an example of where dbInconsistent would cause problems? I've looked at my data and tested it so many times, and while I am sure you're right, I can't tell just how bad of an idea it really is. I don't think I can redesign the tables to make them updateable because it needs to be a many-to-many relationship. The DataT has 60 months worth of data for each line item, and the formulaT contains multiple records per line item (but not down to the month level). The only other way I could think of would be to de-normalize the FormulaT so it lists multiple elements in one line, but that would be super messy and not scalable. Any advice is welcome.

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I don't have anything to hand as its very rare that I use this

    As I said, you need to check the impact in each situation where you consider using it
    The outcome will depend on various factors including the join type, whether you have referential integrity applied and indexing.
    It may have no adverse effects but equally it could be problematic

    The best thing is probably to read up on this with a Google search. Here are 2 links with contrasting viewpoints:
    http://www.utteraccess.com/forum/Dyn...-t1664392.html
    http://https://www.reddit.com/r/MSAc...stent_updates/

    and a brief MS info page:https://support.office.com/en-us/art...7-9e6b55034052

    Hope that helps
    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

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

Similar Threads

  1. Non Updateable Query Help
    By pdowg881 in forum Access
    Replies: 7
    Last Post: 05-22-2015, 07:35 AM
  2. Query not updateable
    By j9070749 in forum Queries
    Replies: 1
    Last Post: 11-04-2013, 10:48 AM
  3. Must use updateable query
    By adams.bria in forum Queries
    Replies: 1
    Last Post: 08-29-2011, 10:31 AM
  4. Need Query To Be Updateable
    By robsworld78 in forum Queries
    Replies: 11
    Last Post: 07-17-2011, 09:06 PM
  5. Non-Updateable Query
    By swalsh84 in forum Queries
    Replies: 4
    Last Post: 04-27-2011, 12:39 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