Recordset Not Updateable...

Posted on 2003-04-01
Medium Priority
Last Modified: 2008-03-10
I have the following query which I wish to use for a data entry form. However, it says the recordset is not updateable when I run the query in datasheet view to test it. Here is the SQL:

   TABLES: tblEmployeeData,tblEmployeeInformation,tblPositions,tblPagers
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName, tblEmployeeData.MI, tblEmployeeInformation.HomePhone, tblPositions.JobCodeID, tblPagers.PagerID
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.EmpID) LEFT JOIN tblPositions ON tblEmployeeData.EmpID = tblPositions.EmpID) LEFT JOIN tblPagers ON tblEmployeeData.EmpID = tblPagers.EmpID
ORDER BY tblEmployeeData.LastName, tblEmployeeData.FirstName;

The tables are linked to a back end DB, and the relationships are all established (one to many), with the PK(EmpID) in tblEmployeeData branching out to the FK(EmpID) in the other 3 support tables.

Ironically, the following SQL *IS* updateable and features 4 tables linked as above.

TABLES: tblEmployeeData,tblEmployeeInformation,tblPositions,tblMailStop-Extension
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName, tblEmployeeData.MI, tblEmployeeInformation.HomePhone, tblPositions.JobCodeID, [tblMailStop-Extension].MailStop
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.EmpID) LEFT JOIN tblPositions ON tblEmployeeData.EmpID = tblPositions.EmpID) LEFT JOIN [tblMailStop-Extension] ON tblEmployeeData.EmpID = [tblMailStop-Extension].EmpID
ORDER BY tblEmployeeData.LastName, tblEmployeeData.FirstName;

Here is something I found interesting that was mentioned by another member:
> From: jbeiswenge
> Date: 01/28/2003 11:23AM PST
> Answer Grade: A
> Points: 5, the following was suggested:
> --- Cut out most of Message ---
> Searching through Access help, it said that if I join 3 or more tables that include
> a one to many relationship - I won't be able to update.  Is there any way around this?

I would like to know if what he said is in fact true (which it does not appear to be based on my second example which is updateable), WHY it is true, where I could go on the net to reference it, and what the best method would be to work around this.

I would be happy to provide more information (such as indexes and such) if needed.

This is my first time using EE, so forgive me if I seem a bit of a newbie. :)
Question by:viperccp
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
  • 4
  • 2
  • 2
  • +2

Expert Comment

ID: 8247738
Make sure all your tables has a primary key constraint on them.

I have never heard the story about the 3 tables before. And I have worked with Access for 10 years!

Expert Comment

ID: 8248250
No 3 table limit I've ever heard of either.  My guess is that the user was confusing some other constraint or taking something out of context.  I will say that when you start editing recordsets and such with multiple tables joined it can get messy especially if you're attempting to undo those changes.  However, I've never heard of any actual constraint on doing so.


Author Comment

ID: 8249340
DrTech: Two tables did not have their own PKs, so I created them for testing purposes, however it did not resolve the issue of making the recordset updateable.

tgraffham: I have never heard of this limitation either, so I will disregard it for now.

*** I have some information I need to correct though! ***

1. The [tblEmployeeInformation] is a "one-to-one" relationship, and not a one-to-many.
2. The [tblMailStop-Extension] is also a "one-to-one" relationship, and not a one-to-many.

Based on this, it turns out that I actually have only one table in the query with a one-to-many relationship, whereas the other tables in the query are one-to-one support tables. With this setup, the recordset *IS* updateable.

However, when I add [tblPagers] which is a "one-to-many" relationship to the main table [tblEmployeeData], I now have 2 tables that are a one-to-many relationship from the main table and the recorset becomes *NOT* updateable.

*** Can someone confirm that this is inherently how queries work, that you can not update a recordset that has two tables where there could be multiple records returned for a single record in the main table?

I understand subforms would be a sufficient way to get around this, however I would hate to have to create a subform for just one field.
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!


Expert Comment

ID: 8251421
Did you remember to Re-Link the tables after adding the missing primary key constraints?

Author Comment

ID: 8255707
DrTech: Actually, the two support tables never really required a PK, they are linked by their FK, so there was no re-linking necessary. I just created the primary keys in the two support tables as you suggested in the event that I was missing something. The support tables do not require PK's, just the primary table that they are linked to.
Let me know if I am wrong on this, and if so, where would I link the PK fields in the support tables to? To the best of my knowledge, you can't link a PK from one table to a PK in another table, it just doesn't make sense to me.

Author Comment

ID: 8255741
DrTech: Perhaps I am mis-understanding what you mean by adding primary key "constraints" on all the tables. I can tell you that the FK in the support tables are indexed, however they are not set to primary (which would make them a PK), and they are not set to force Unique values.

Expert Comment

ID: 8257981

Just to clarify.  What DrTech means is that each table must have ITS OWN primary key.  Granted, a table that you link to another table on a one-to-many will have a foreign key in it that the other table's primary key is related via.  However, that first table MUST also have it's one primary key separate from that foreign key.  Do all your tables have this?

General naming convention stuff:

Table1 (ID (primary key), Field1, Field2)
Table2 (ID (primary key), Table1ID (foreign key), Field1, Field2)

Given the above, ALL your tables should have the ID primary key regardless of whether or not they have foreign keys defining some relationship to one or more other tables.

Do you have these keys?

Accepted Solution

viperccp earned 0 total points
ID: 8262579

I did add the PK (using the convention you mentioned above) in my test, and it did not change the results. I have been programming in MS Access for over 8 years, and I have never reallized a need to create PK's in support tables, unless they themselves will be linked to their own set of support tables. Why is this necessary? Or is it more of a standard, such as naming conventions. Not necessary, but a good idea for standardization purposes.

Again, I did add PK's to all the tables in the query, and the recordset was still not updateable. This happens every now and then with query's I create and I always work around the problem (via subform or subquery's), however I would like to "understand" what is going on and "what" exactly is causing the query to become NOT updateable.

Also, When I take out the tblPagers (which is one-to-many), the query is updateable, regardless of whether the support tables have PK's defined or not.
LVL 28

Expert Comment

ID: 8677556
No comment has been added lately (66 days), so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area for this question:

RECOMMENDATION: Accept viperccp's answer http:#8262579 and Refund

Please leave any comments here within 7 days.



EE Cleanup Volunteer
If you feel that your question was not properly addressed, or that none of the comments received were appropriate answers, please post a request in Community support (with a link to this page) to refund your points. http://www.experts-exchange.com/Community_Support/

Expert Comment

ID: 8755729
Per Recommendation

EE Moderator

Featured Post

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!

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Suggested Courses

762 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