Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 314
  • Last Modified:

need workaround - nonupdatable recordset

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
terpsichore
Asked:
terpsichore
  • 3
  • 3
1 Solution
 
PatHartmanCommented:
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
 
terpsichoreAuthor Commented:
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
 
PatHartmanCommented:
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!

 
terpsichoreAuthor Commented:
I removed that count field and still get the error...
0
 
PatHartmanCommented:
Source_Language_Lo...  Is missing a primary key and so is Target_Language_Lo.
0
 
terpsichoreAuthor Commented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now