Solved

need workaround - nonupdatable recordset

Posted on 2014-04-22
6
306 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
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 34

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

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

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

911 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

16 Experts available now in Live!

Get 1:1 Help Now