Solved Dreaded Read-Only Query

[Deleted member 147267]

IF you want to arrive at a definitive conclusion I think you have no option at this point but to post a copy of your db. I say if because on one hand it seems that you're content to carry on with your current fix but on the other hand, the discussion is still being kept alive by us. I guess we're a bunch of inquisitive types that love a challenge and don't like to see things go unresolved. While your fix may appear to be the approach you want to take, you may get inconsistent updates, which may cause a data mess-up that can be hard to straighten out.

If you can't post a db copy that's fine. Then the last thing I might recommend is in a copy of the query, try eliminating all tables that don't seem relevant to the fields you mention that are involved and switch this to a select query. If a field you're trying to join is one that you think ought to return one record but returns more than one, then you might be on to something. So if the other fields repeat data but these 'suspect' fields contain unique values, that could be an indicator that those fields are contributing to the problem. Not sure if how I expressed this will make sense; if not, I believe that you should post a db rather than risk mucking up your data with inconsistent updates.

 

[The_Doc_Man]

Micron's suggested experiment - to turn the UPDATE into a SELECT and see how many records come back (and see where they differ and where they are similar) might help. BECAUSE it might allow you to add a criterion clause to your WHERE that would eliminate all but one record in the SELECT, at which point you could reverse it back to an UPDATE. And it would demonstrate what we mean by saying that the UPDATE has to specify records unambiguously.

Clearly, when you remove that one JOIN as you described, the ambiguity vanishes, so whatever went wrong has to do with tblSubjectAddresses. Something about that table has introduced the problem. Verify that the JOIN to tblSubjectAddresses involves an indexed field. It doesn't have to be a PK but it DOES have to have an index in order to be part of a JOIN.

Sorry if I went over your head there, but it IS a knotty little technical problem. Sometimes it is hard to tell how far I can go with explanations, which is why I sometimes go a little too far. All I can say is, I didn't intend to lose you along the trail. My bad.

 

[Sonnydl]

If you can't post a db copy that's fine. Then the last thing I might recommend is in a copy of the query, try eliminating all tables that don't seem relevant to the fields you mention that are involved and switch this to a select query.

Unfortunately, I cannot post a copy of the db. The original query is a select query that I then use to create an Recordset for VBA use. Only if one is successful in sending the email, does the Sub then trigger an edit two of the fields. Per an earlier post:

"I already have a workaround (I've created a separate Sub to get the records that are part of the original queries, but with only the essential fields, and use the code snippet above), but I was trying to understand why joining the tblSubjectAddresses had "broken" the original process. In essence, what is it about that join that makes the recordset become read-only?"

 

[The_Doc_Man]

Re-reading your posts and looking more closely at the relationship diagram, a bell finally went off.

Remember that article about why a query might not be updateable? Remember this line item?

"It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables. "

Look at the arrows in tblSubjects that is the link between tblSubTravelerProfile and tblSubjectAddresses. Remember that Access can see the relationships when looking at queries as it tries to analyze them for optimum execution. There is your problem. If tblSubjects IS involved then you have mixed-direction relationships. If you DON'T use it, there is NO direct relationship between tblSubTravelerProfile and tblSubjectAddresses. So you can't JOIN them. Except... that you CAN make a sub-query that contains the proper JOINS between tblSubjects and tblSubjectAddresses, THEN in your main query execute the rest of the JOIN with that query. That forces isolation of the contrary relationship directions.

 

[Sonnydl]

Micron's suggested experiment - to turn the UPDATE into a SELECT and see how many records come back (and see where they differ and where they are similar) might help. BECAUSE it might allow you to add a criterion clause to your WHERE that would eliminate all but one record in the SELECT, at which point you could reverse it back to an UPDATE. And it would demonstrate what we mean by saying that the UPDATE has to specify records unambiguously.

Okay, so here's some more background to clarify. I almost always have more than one record resulting from the query. This was the case before the new Addresses JOIN (when the address fields were part of the Profiles table) and still is after the JOIN. Worked pre-JOIN, stopped working post-JOIN.

 

[Sonnydl]

Look at the arrows in tblSubjects that is the link between tblSubTravelerProfile and tblSubjectAddresses. Remember that Access can see the relationships when looking at queries as it tries to analyze them for optimum execution. There is your problem.

So I could be mistaken about my interpretation of "directional relationships," but everything is an INNER JOIN, there are no LEFT or RIGHT JOINS in the SQL. I don't believe there are any genuine arrows in the diagram. A genuine arrow looks like this:

 

[Deleted member 147267]

Look at the arrows in tblSubjects

I don't see any arrows there; they all look like equal joins to me. Addresses>regions and addresses>countries I do.
EDIT - OK the picture is a bit hard to be sure about - maybe what I thought are arrows are dots. Your sql on page 1 here doesn't reflect any outer joins.

 

[Sonnydl]

I don't see any arrows there; they all look like equal joins to me. Addresses>regions and addresses>countries I do.

So those two JOINS are different because they come from a different linked backend Dbase--one that shares tables across multiple projects (things like countries, regions, time zones, currencies, etc. that are the same for all the projects).

I removed each of those joins (and the data in the query) individually to see if it would fix it. It does not. BTW, for prior projects, where I don't have the tblSubjectAdresses table and just fields in the profiles, those same two tables and joins exist, and the query doesn't lock as read-only.

 

[Sonnydl]

So in my quest to drive myself to the brink (I can't seem to let this go, just yet...), I moved some relationships around to make sure they were correct.

These are the primary database relationships:

I removed the tblSubjectAddresses from my query and just added tblSubjects. The resulting query is editable. Throw the Addresses on there, it's read-only.

 

[Deleted member 147267]

If it's not because there are SubjectID values in profiles but not in addresses while at the same time you're imposing criteria on addresses
AND ( ( tblsubjectaddresses.primary ) = true ) then I'm all out of ideas.
You didn't say why you can't post a db, so if it's about data privacy then there are 2 posts here for scrambling data. If you're concern is about protecting a commercial app then you might be able to copy a db and remove everything that would expose what that is and leave in only the tables and queries involved.

Regardless, if it were me I wouldn't take a chance on allowing inconsistent updates so for me it's either post a db or maybe you can do any updates separately (in series). Beyond that, I figure there's nothing else I can contribute.
Good luck! I hope you solve it.

 

[Sonnydl]

SOLVED-ISH: Okay, so I went back to the source database BE, and created a simple new table (three field: ID; SubjectID_FK; Test) that I linked to to tblSubjects.SubjectID, created a very simple three-table query with tblTest--tblSubjects--tblSubjTravelerProfiles, and was also unable to edit the data. There seems to be something about both tables in the query being linked to the same FK that makes it read-only (in this instance, at least.) If I do a query where the same table are linked, but they join to different FKs, then the query is not read-only.

Read-Only:

Editable:

To confirm this, I created three, completely new, simple test Tables linked to the same FK field, and created a query query with all three tables:

The result is a READ-ONLY query.

So I believe I now know what causes the issue. I don't quite know why (what's the theory, reasoning, etc. that explains it) but I know what. And that seems like a great outcome to me.

I'm hoping someone's going to palm slap their forehead and say, "Of course! Yeah, you can't do that because...."

 

[The_Doc_Man]

My poor choice of nomenclature regarding arrows. Let's look at post #29. It isn't that the INNER JOIN (which is an equi-JOIN as you put it) has arrows one way or the other. The relationships have a direction implied by one-side vs. many-side. It is that the one-side is different in ONE case only. If you look at the direction of the one/many relationships for tblSubjects, the two relationships have the one side on tblSubjects. Let me do a walkthrough for you because Access is trying to isolate a given record for you.

Start with a particular reimbursement. Your table with a given Reimbursement ID associates it with a given travel item. So you follow the Travel Item ID to the Booked Items. That ties the chain of information to Subject Bookings through the Subject Booking ID and because it is many-to-one, you have a unique record being referenced. Not done yet, though, because you want to tie that to a person via the TravelerID. No problem. Every one of those relationships is many-to-one in the direction I'm going so nothing is ambiguous so far. Each JOIN only gives you one record. Supposedly your traveler will have some subject that was related to the travel, and you tie that in to the Subject ID. Still following a many-to-one relationship, so these JOINs will be consistent and singular. But now you want to use the subjectID to find an address and that is one-to-many i.e. the one-side is pointing the other way. Access doesn't like that change. When you remove the Addresses table that relationship leaves the picture.

EDIT: You and I must have posted almost simultaneously. As your experiment demonstrates, the implied direction change of the relationship even in a simplified case still makes it balk. Access does not like that "one-side vs. many-side" inconsistency.

 

[Sonnydl]

You and I must have posted almost simultaneously. As your experiment demonstrates, the implied direction change of the relationship even in a simplified case still makes it balk. Access does not like that "one-side vs. many-side" inconsistency.

That's an explanation I can (somewhat) understand. And even if I don't fully get the why of the conflict, there's a logic to it that I can accept. Thank you!

 

[isladogs]

Sonny
You've posted several screenshots of query joins and relationships but, if I'm not mistaken, have never posted screenshots of ALL the joins used in your read only query or the one used as a workaround. I know you did PARTIAL screenshots but not the COMPLETE picture. Perhaps that might show some light on the situation

 

[Deleted member 147267]

DocMan, with all due respect that sounded a bit like conjecture to me so I did a test.
If I have properly constructed a query with tables and joins as shown earlier then such a query IS updatable if it is properly indexed. I and others said as much a long time ago. See below where I was able to edit bark to barks, but not before adding unique indexes. I suppose in my right table RightID should be named MidID but it matters not for this demo.

 

[The_Doc_Man]

@Micron
It was indeed a conjecture supported by at least some personal experience. But in that demo you posted leaves me with a question. What SQL did that query generate?

There is a further question. It APPEARS that you had one-to-one relationships based on the way the relationship lines were displayed in the upper part of the query design box. The OP is dealing with a one-many case.

 

[Deleted member 147267]

What SQL did that query generate?

SELECT tblALeft.LeftVal, tblAMid.MidVal, tblARight.RightVal
FROM (tblAMid INNER JOIN tblALeft ON tblAMid.ID = tblALeft.MidID)
INNER JOIN tblARight ON tblAMid.ID = tblARight.RightID;

Re relationships:
- first of all, I had no relationships defined when I posted that but I do now and nothing changes except for the line endings in the query. When you create relationships on non-indexed fields, Access defines them as one to many. As soon as you change a related foreign key field to a unique index, Access defines it as one to one. Relationship and query join line endings change appearance as you no doubt know. So yes, I agree that the OP has a one to many relationship, but I say that's because there is no unique index on the field that requires it in order for this to work.

 

[The_Doc_Man]

Well, we have been dancing around the mulberry bush of the article on why a query might be non-updateable, and one of the items in that article was that one or more fields lacked a proper index. A proper relationship cannot be formed on fields that are not indexed appropriately to their purpose. So I have been repeating the fact that Access didn't like it. If your suggestion can be applied to what is being done by the OP, then that should fix it. The question will be whether a unique key is possible in his case. He is working on a field that is a common non-unique property of multiple records. The relationship isn't based on the PK of that table but rather on one of its attributes.

 

[Deleted member 147267]

and one of the items in that article was that one or more fields lacked a proper index

Yes, I believe you did, and I raised this as a probable cause in my first post (4)

Lastly, you reviewed AB's website of reasons for this, so are you saying that it cannot be due to a lack of indexing somewhere?

 

[The_Doc_Man]

Micron, as you probably realize, we often raise several likely causes for a given problem. I agree you pointed out that indexing could be an issue. The repetition occurs because the details of the problem have developed (and others have chimed in.) I'm surely not disputing that you suggested the solution. Part of this "back and forth" was an attempt to answer the bolded question in post #23.