none
Can't Edit Some Query Results in Web App RRS feed

  • Question

  • Hi all! I'm putting together a web app for the first time (and have some, limited, DB experience in general) and I've hit a snag that I can't seem to find an easy way around. I'm trying to pull together rows from two tables to present in a single view, which was pretty straightforward, and make them all editable, which is the issue.  The issue is that, both in the online Sharepoint view and in the datasheet view in Access itself, fields from the "people" table (on the many side) are editable but fields from the "studentInfo" table (one the one side) are not. By "not editable" I mean I get a message saying that the underlying field is read-only. I'm aware of this article:

    http://support.microsoft.com/kb/2151084

    However, I don't think that's the issue, as the data isn't editable on the designer side and the article implies that I should be seeing this issue the other way around (i.e., that fields from the table on the many side should be uneditable, the opposite of what I'm seeing).

    Not sure if there's something simple I'm doing wrong that would enable this. If there's a functional issue here, I have a couple potential solutions that I don't know how to execute. The first is that this really ought to be a one-to-one relationship, but I don't know how to do that using Lookup types (everything I try just ends up being one-to-many). If that's not a possibility, it seems like using a subview that only touches the data from one table may work, but I don't know how to pass data to another view like that internally. Any info on how to debug this would be most appreciated. Major thanks to anyone who chimes in, this has been really frustrating just because this feels like it ought to be such a small issue. 
    Sunday, January 5, 2014 2:56 AM

All replies

  • You are mis-interpreting the article. It says:

    "This is by design. The Access Services Query Processor can update only
    the columns from the recordset at the last level in the join, which is
    sometimes called the "most many" portion of the query."

    That means for your situation that the many side cannot be updated.

    The standard way to allow updating of both tables is to offer a form (representing the one-side) with a subform (representing the many-side).


    -Tom. Microsoft Access MVP

    Sunday, January 5, 2014 4:29 AM
  • You are mis-interpreting the article. It says:

    "This is by design. The Access Services Query Processor can update only
    the columns from the recordset at the last level in the join, which is
    sometimes called the "most many" portion of the query."

    That means for your situation that the many side cannot be updated.

    The standard way to allow updating of both tables is to offer a form (representing the one-side) with a subform (representing the many-side).

    I'm confused by your reply, Tom.  I interpret the article as saying that the "many" side should be updatable, and the "one" side should not -- which is in fact what Reiik is reporting.  Am I wrong?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 5, 2014 4:37 AM
  • Yes, you're right. I stand corrected. The KB article indicates that the "most many" side should be editable.

    Still my advice to use a 1:M form stands. I have never been in favor of finessing it and hoping that SOME of the fields in a 2-table join would be updatable where others would not. If developers have a hard time understanding this, so much more would average users.


    -Tom. Microsoft Access MVP

    Sunday, January 5, 2014 5:07 AM
  • Yes, you're right. I stand corrected. The KB article indicates that the "most many" side should be editable.

    Still my advice to use a 1:M form stands. I have never been in favor of finessing it and hoping that SOME of the fields in a 2-table join would be updatable where others would not. If developers have a hard time understanding this, so much more would average users.

    The issue here, as I understand it, is specific to Access Services, and hence web databases.  I've never had much difficulty creating fully updatable multi-table queries in an Access client database.  Although subforms are good for most purposes, multi-table queries have their uses.

    I have no experience with Access 2010 web databases.  But in the context of forms & subforms in such databases, I'm concerned about this statement from the article:  "You will see similar behavior in Web forms with subforms on them. Only the data on the subform may be edited."  That seems to be saying that a form/subform arrangement wouldn't allow you to update the "one-side" data as presented on the main form.  Can you verify that?


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 5, 2014 5:19 AM
  • I am using my available time for A2013 web apps. A2010 web apps are a dying breed.


    -Tom. Microsoft Access MVP

    Sunday, January 5, 2014 5:28 AM
  • Thanks both of you for your input! Just to clarify, this is a 2013 web app, and I am also unable to edit the entries even on the "client" / developer side - if there is such a thing with a 2013 web app and it's not just editing the remote data on the server.

    The subforms / subviews issue seems to be a good solution. I've tried to find information for how to pass the data from the view to the subview with little luck (the documentation for 2013 web apps is a bit sparse), would anyone be able to give me a quick pointer on what high-level operations I need to do? Step-by-step shouldn't be necessary, I get most of it at this point.

    EDIT: I missed Dirk's comment stating that subviews may not work either. I'd still like to try just to verify one way or the other.

    • Edited by Reiik Sunday, January 5, 2014 5:35 AM
    Sunday, January 5, 2014 5:33 AM
  • A2010 web apps are a dying breed.

    I agree, but I have no experience with them, and I thought that was what Reiik was using.  It seems I was wrong, but then I wonder if the cited article even applies.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Sunday, January 5, 2014 5:38 AM