Results 1 to 7 of 7
  1. #1
    aqueousdan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2016
    Posts
    20

    "This recordset is not updateable"... Not sure why

    I have created a query to pull information out of various tables to eventually use in a form. I can edit the information inside that specific table but not in the query itself.



    Can anyone see why this is happening?
    Attached Files Attached Files

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Does your query use a JOIN? When queries retrieve data from more than one table, they are generally not updatable. In order to update a query with a JOIN, you need to start by making sure the JOIN represents the proper relationship and the JOINED columns are Indexed.

  3. #3
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Does your query use a JOIN? When queries retrieve data from more than one table, they are generally not updatable.
    I have to disagree with that statement. I use queries that join multiple tables that are updateable all the time. It all depends on the type of relationship that exists between the two queries.

    See the following for reasons why certain ones are not updateable:
    http://rogersaccessblog.blogspot.com...eable-why.html
    http://www.techrepublic.com/blog/mic...s-wont-update/

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 8 Access 2013
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by JoeM View Post
    I have to disagree with that statement. I use queries that join multiple tables that are updateable all the time. It all depends on the type of relationship that exists between the two queries.

    See the following for reasons why certain ones are not updateable:
    http://rogersaccessblog.blogspot.com...eable-why.html
    http://www.techrepublic.com/blog/mic...s-wont-update/
    Disagreeing is certainly your prerogative. However, I am not sure why you quoted me out of context.

  5. #5
    aqueousdan is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Sep 2016
    Posts
    20
    So how do I change mine so the relationship allows updating?

  6. #6
    JoeM is offline VIP
    Windows 7 32bit Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Disagreeing is certainly your prerogative. However, I am not sure why you quoted me out of context.
    I was simply specifying that part of the statement I disagree with. I think that part of your statement is just too broad/general a statement, and is not necessarily true. It is really dependent on the underlying factors.
    From my sixteen years of personal experience, most of the queries are I write are updateable (making sure that your database tables are normalized and the queries are constructed properly helps).

    So how do I change mine so the relationship allows updating?
    I cannot download your database to take a look at it from my current location.
    I would recommend going through the links I provided, and see if you can determine what situation your query falls into that is preventing it from being updateable.
    The first thing to check is the nature of the relationship. Is it many-to-many?

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    sometimes you can do it by changing the recordset type to dynaset - inconsistent updates, but not in this case. The reason why is because of the multiple link between factories and engineers. Remove the Site link in the query and the query will become updateable.

    You also need to be clear about 'updateable' - it is one thing to change a value in an existing record, quite another to add a new record - in your query you are showing SiteID and FactoryID only once (for the Sites and Factories tables respectively), if you added a new record based on this then both new Factory and Engineers records would not have their family keys populated and become orphans.

    If the factory/engineer relationship is valid then you will need to handle it in forms - the engineers would be in a subform and you would set the linkchild and linkmaster properties of the subform to

    FactoryID;SiteID

    then remove engineers from your query

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

Similar Threads

  1. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  2. "Operation Must Use an Updateable Query" Problem
    By McArthurGDM in forum Queries
    Replies: 1
    Last Post: 05-28-2015, 11:15 AM
  3. Replies: 4
    Last Post: 08-12-2014, 08:47 AM
  4. "This recordset is not updateable"
    By AccessNewb11 in forum Access
    Replies: 2
    Last Post: 07-31-2014, 10:36 AM
  5. "Recordset not updateable"
    By yawkey in forum Access
    Replies: 1
    Last Post: 07-24-2014, 04:40 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