Solved

need workaround - nonupdatable recordset

Posted on 2014-04-22
6
308 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 36

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 36

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

820 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