MartyConnelly
martyconnelly at shaw.ca
Thu Oct 27 18:31:53 CDT 2005
It could be many things so here is a checklist How to troubleshoot errors that may occur when you update data in Access queries and in Access forms http://support.microsoft.com/default.aspx?scid=kb;en-us;328828 Also Have a look at the sample mdb's here especially update and query ones http://www.rogersaccesslibrary.com/TableOfContents3.asp Some other things Basically, the query is non-updateable if there are many-to-many or many-to-one relationships represented in the output. Access (actually SQL in general) can't do updates in that case because it means that it would be trying to update multiple rows in one or more of the underlying tables for each of the individual rows that you are trying to update in your form. If your SQL includes some functions that modify the data that's in one or more of the tables (e.g., the Left$'s) that too would make the resulting recordset non-updateable since there's no way for Access (or SQL) to accept changes to a field that is the result of a function. To be updateable, all output from the query(ies) must be the original fields without modification, calculation, concatenation, etc. Trying to do a 1-to-many-to-1 join in the query, this produces unique records, but access seems to dislike this approach and makes the recordset non updateable. Admin Sparky wrote: >Based on what I saw, it appears there is nothing inherently wrong with how >I've normalized the data. However, my question seems to be...now that I have >this data so nicely normalized, how can I query it in a meaningful way so as >to be able to update the data? Ideally, the data entry person would like to >see a list of people along with their equipment and location, then be able >to change any portion of the information as reassignments occur. > Mark > > > On 10/27/05, MartyConnelly <martyconnelly at shaw.ca> wrote: > > >>Maybe have a look at this data model for ideas and it's associated >>business rules. >> >>http://www.databaseanswers.org/data_models/inventory_of_servers_in_wyoming/index.htm >> >>There are a couple hundred here too. >>http://www.databaseanswers.org/data_models/index.htm >> >>Admin Sparky wrote: >> >> >> >>><For best viewing, a mono-spaced font is recommended.> >>>Group, >>>Please comment on the table structure noted below. I chose this >>> >>> >>particular >> >> >>>scheme because locations are the least likely to change and are forming >>> >>> >>the >> >> >>>cornerstone of an Access-AutoCAD application. My problem concerning data >>>updates is that equipment is most easily identifiable with a person, not >>> >>> >>a >> >> >>>location. But, a non-updateable recordset occurs when these tables are >>>combined in a query. So...what is an acceptable method of approaching >>> >>> >>this >> >> >>>situation? >>>|--tblLocPers---tblPersonnel >>>| >>>|--tblLocPC-----tblPCs >>>tblLocations--| >>>|--tblLocMon----tblMonitors >>>| >>>|--tblLocPrntr--tblPrinters >>>Mark >>> >>> >>> >>> >>- >> -- Marty Connelly Victoria, B.C. Canada