viperccp
asked on
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,tblEmploye
--------------------------
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName,
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.Emp
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,tblEmploye
--------------------------
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName,
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.Emp
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. :)
--------------------------
TABLES: tblEmployeeData,tblEmploye
--------------------------
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName,
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.Emp
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,tblEmploye
--------------------------
SELECT tblEmployeeData.EmpNbr, tblEmployeeData.LastName, tblEmployeeData.FirstName,
FROM ((tblEmployeeData LEFT JOIN tblEmployeeInformation ON tblEmployeeData.EmpID = tblEmployeeInformation.Emp
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. :)
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.
Tim
Tim
ASKER
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.
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.
Did you remember to Re-Link the tables after adding the missing primary key constraints?
ASKER
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.
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.
ASKER
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.
viperccp,
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?
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
viperccp,
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Thanks,
TextReport
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. https://www.experts-exchange.com/Community_Support/
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.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
Thanks,
TextReport
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. https://www.experts-exchange.com/Community_Support/
Per Recommendation
PashaMod
EE Moderator
PashaMod
EE Moderator
I have never heard the story about the 3 tables before. And I have worked with Access for 10 years!