Recordset Not Updateable...

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. :)
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!
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.

viperccpAuthor Commented:
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.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Did you remember to Re-Link the tables after adding the missing primary key constraints?
viperccpAuthor Commented:
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.
viperccpAuthor Commented:
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.

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?
viperccpAuthor Commented:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
Per Recommendation

EE Moderator
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.