Solved

need workaround - nonupdatable recordset

Posted on 2014-04-22
6
305 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
  • 3
  • 3
6 Comments
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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
Comment Utility
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 34

Expert Comment

by:PatHartman
Comment Utility
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:terpsichore
Comment Utility
I removed that count field and still get the error...
0
 
LVL 34

Accepted Solution

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

Author Closing Comment

by:terpsichore
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now