Solved

need workaround - nonupdatable recordset

Posted on 2014-04-22
6
311 Views
Last Modified: 2014-04-23
Dear experts -
I have a continuous form; my goal is to allow users to select multiple records via a checkbox. This checkbox was initially set up using a temp table, linked to the main table.

The main table has a lot of other linkages, so that we can display other fields in a user-friendly way.

This HAD been working fine, but now all of a sudden is not - and I can't trace the reason. One person told me that any table linked to others would make the recordset nonupdatable (although this is not the case in many other places in our system).

The main table IS linking to a few other queries - NONE of them has any calculations.

ALL underlying tables have primary keys.

Any ideas how to work around this, or come up with an alternative solution?
0
Comment
Question by:terpsichore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 38

Expert Comment

by:PatHartman
ID: 40016035
It isn't the number of tables involved in the join that causes the query to be not updateable.

If it used to work but now doesn't, then something about the queries changed.

Did you add any aggregation?  Group By, Sum, Avg, etc?
Did you add additional tables?  How are the new tables related to the original tables?

In order for a multi-table join to be updateable (other things not withstanding), all the tables need to be in a single path of a hierarchical relationship or the tables may be "lookup" tables.  Make certain that you have not added a table from a parallel path.

For example a student may have vehicles and he may have pets.  Even though both vehicles and pets are related to students and include the foreign key StudentID, they are not related to each other and they are not in the same path of the hierarchy, they are parallel.  That means that not only can you not create an updateable query that includes all three tables but you cannot even make a query that produces a recordset that makes sense.  The upshot of this three-way join is a Cartesian Product and the number of rows in the vehicles table will cause the pets to multiple.  So if Sam has a moped and a convertible and a cat named fluffy, this query will produce:
Sam, moped, Fluffy
Sam, convertible, Fluffy
And it gets worse if Sam also has a goldfish named Stinky
Sam, moped, Fluffy
Sam, moped, Stinky
Sam, convertible, Fluffy
Sam, convertible, Stinky
0
 

Author Comment

by:terpsichore
ID: 40016052
Dear Pat -
this is what the query looks like graphically - perhaps you can shed some light on whether this is/can be updatable...
basically, I have a core table of vendors, and then all of the fields are essentially looking up text values (lookups) based on key fields.
The (local) temp table to the left, as I noted, is used solely to allow the local user to select one or more records and then perform actions based on various controls.
sql schema
0
 
LVL 38

Expert Comment

by:PatHartman
ID: 40016152
I'm going to guess that Vendor_ActionCount is the culprit.  Did you add that one recently?  It sounds like it counts things and that would make it not updateable.  If any part of a query is not updateable, Access flags the entire query as not updateable.

If this is the recordsource for a form, you might be able to solve the problem by using DLookup() to get the count in the form.  DLookup() runs a separate query for each row so it should never be used in a query.  If your query returns a thousand rows, it will run a thousand Dlookup() queries.  As you can imagine, the larger your recordset, the slower it will be.  With a form, the recordset should be limited to only a small number of rows.  If your query has no criteria, I strongly recommend adding some to reduce the number of rows returned because you will be unhappy about the performance if you don't.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:terpsichore
ID: 40016203
I removed that count field and still get the error...
0
 
LVL 38

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40016278
Source_Language_Lo...  Is missing a primary key and so is Target_Language_Lo.
0
 

Author Closing Comment

by:terpsichore
ID: 40016871
wow - thanks, I had overlooked that (it was a big surprise that there was no primary key on that table). I fixed that and removed the calculated field and it works fine. THANK YOU.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question