Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 448
  • Last Modified:

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. :)
0
viperccp
Asked:
viperccp
  • 4
  • 2
  • 2
  • +2
1 Solution
 
DrTechCommented:
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!
0
 
tgraffhamCommented:
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
0
 
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.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

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

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.
0
 
TextReportCommented:
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. http://www.experts-exchange.com/Community_Support/
0
 
PashaModCommented:
Per Recommendation

PashaMod
EE Moderator
0

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

  • 4
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now