Solved Dreaded Read-Only Query (1 Viewer)

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
I have a query from which I create a recordset which I then use to build an email (requesting payments be sent). If the email is confirmed sent, I use rs.edit / .update in VBA to mark the needed changes which will then remove the record from the request queue. This has been working well for quite some time. For a new project, I had to revise the database to allow for multiple addresses (one of which is marked primary) which is included in the recordset. However, in doing so, the recordset is now Read-Only and therefore cannot be updated.

I looked at Allen Browne's "Why is my query read-only?" (http://allenbrowne.com/ser-61.html), but I cannot identify the source of the problem. I've done a workaround by creating a new query just to allow for the update, but I'd like to learn what is the source of the issue, so that I can avoid it in the future.

Working/Editable Query's SQL:
Code:
SELECT tblSubjTravelerProfiles.SubjID_FK, tblStudies.Nickname, tblSubjectBookings.SubjBookingID, tblSubjTravelerProfiles.FirstName, tblSubjTravelerProfiles.LastName, tblSubjTravelerProfiles.HomeAddress, tblSubjTravelerProfiles.City, tblSubjTravelerProfiles.PostalCode, tblCountryRegions.DisplayedRegion, tblCountries.CountryAbbreviation, tblSubjTravelerProfiles.EmailAddress, tblBookedItems.ConfNumber, tblItinReimburements.ReimbItemID, tblItinReimburements.TravelItemID_FK, tblItinReimburements.DateOfExp, tblItinReimburements.Description, tblItinReimburements.Units, tblItinReimburements.UnitCost, [Units]*[UnitCost] AS Total, tblItinReimburements.Requested, tblItinReimburements.Sent
FROM tblStudies INNER JOIN (((tblSubjTravelerProfiles INNER JOIN tblCountries ON tblSubjTravelerProfiles.CountryID_FK = tblCountries.CountryID) INNER JOIN tblCountryRegions ON tblSubjTravelerProfiles.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN (((tblStudyVisitSchedule INNER JOIN tblSubjectBookings ON tblStudyVisitSchedule.StudyVisitID = tblSubjectBookings.StudyVisitID_FK) INNER JOIN tblBookedItems ON tblSubjectBookings.SubjBookingID = tblBookedItems.SubjBookingID_FK) INNER JOIN tblItinReimburements ON tblBookedItems.TravelItemID = tblItinReimburements.TravelItemID_FK) ON tblSubjTravelerProfiles.TravelerID = tblSubjectBookings.TravelerID_FK) ON tblStudies.StudyID = tblStudyVisitSchedule.StudyID_FK
WHERE (((tblSubjTravelerProfiles.SubjID_FK)=[Reports]![rptUnwrittenChecks]![txtSubjectID]) AND ((tblItinReimburements.DateOfExp)<=Now()) AND ((tblItinReimburements.UnitCost)<>0) AND ((tblItinReimburements.Sent) Is Null))
ORDER BY tblItinReimburements.DateOfExp, tblItinReimburements.Description;

Read-Only Query's SQL:
Code:
SELECT tblSubjTravelerProfiles.SubjID_FK, tblStudies.Nickname, tblSubjectBookings.SubjBookingID, tblSubjTravelerProfiles.FirstName, tblSubjTravelerProfiles.LastName, tblSubjectAddresses.Primary, [Address1] & IIf([Address2]<>"","; " & [Address2],"") AS PrimAddress, tblSubjectAddresses.City, tblSubjectAddresses.PostCode, tblCountryRegions.DisplayedRegion, tblCountries.CountryAbbreviation, tblSubjTravelerProfiles.EmailAddress, tblBookedItems.ConfNumber, tblItinReimburements.ReimbItemID, tblItinReimburements.TravelItemID_FK, tblItinReimburements.DateOfExp, tblItinReimburements.Description, tblItinReimburements.Units, tblItinReimburements.UnitCost, [Units]*[UnitCost] AS Total, tblItinReimburements.Requested, tblItinReimburements.Sent
FROM ((tblSubjects INNER JOIN tblSubjTravelerProfiles ON tblSubjects.SubjectID = tblSubjTravelerProfiles.SubjID_FK) INNER JOIN (((tblStudyVisitSchedule INNER JOIN tblStudies ON tblStudyVisitSchedule.StudyID_FK = tblStudies.StudyID) INNER JOIN tblSubjectBookings ON tblStudyVisitSchedule.StudyVisitID = tblSubjectBookings.StudyVisitID_FK) INNER JOIN (tblBookedItems INNER JOIN tblItinReimburements ON tblBookedItems.TravelItemID = tblItinReimburements.TravelItemID_FK) ON tblSubjectBookings.SubjBookingID = tblBookedItems.SubjBookingID_FK) ON tblSubjTravelerProfiles.TravelerID = tblSubjectBookings.TravelerID_FK) INNER JOIN ((tblSubjectAddresses INNER JOIN tblCountryRegions ON tblSubjectAddresses.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN tblCountries ON tblSubjectAddresses.CountryID_FK = tblCountries.CountryID) ON tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK
WHERE (((tblSubjTravelerProfiles.SubjID_FK)=[Reports]![rptUnwrittenChecks]![txtSubjectID]) AND ((tblSubjectAddresses.Primary)=True) AND ((tblItinReimburements.DateOfExp)<=Now()) AND ((tblItinReimburements.UnitCost)<>0) AND ((tblItinReimburements.Sent) Is Null))
ORDER BY tblItinReimburements.DateOfExp, tblItinReimburements.Description;

The difference is the JOIN "tblCountryRegions ON tblSubjectAddresses.RegionID_FK = tblCountryRegions.RegionID) INNER JOIN tblCountries ON tblSubjectAddresses.CountryID_FK = tblCountries.CountryID) ON tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK" If I remove this, it becomes editable again. I just don't know why. And yes, SubjectID and TravelerID_FK are the one-to-many fields in the Relationship manager (I just didn't bother to rename TravelerID_FK to SubjectID_FK.)
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:54
Joined
Feb 19, 2013
Messages
16,555
the more joins you have, the more likely the query cannot be edited. It is also important to make sure relationships are properly defined - it won't always make a difference but always worth doing.

If you are only updating one table, it is also worth checking how your joins are done, it might be by removing a join and using criteria instead will solve the problem. So perhaps try this as criteria tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK

The other thing you can check is the query (and subsequent form) has the recordset type set to dynaset - inconsistent updates

but at the end of the day, it may be the query is just too complex
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:54
Joined
May 21, 2018
Messages
8,463
Pretty unreadable. You can use a SQL formatter. Put my money is that one of those inner joins is not on a PK.

Code:
SELECT tblsubjtravelerprofiles.subjid_fk,
       tblstudies.nickname,
       tblsubjectbookings.subjbookingid,
       tblsubjtravelerprofiles.firstname,
       tblsubjtravelerprofiles.lastname,
       tblsubjtravelerprofiles.homeaddress,
       tblsubjtravelerprofiles.city,
       tblsubjtravelerprofiles.postalcode,
       tblcountryregions.displayedregion,
       tblcountries.countryabbreviation,
       tblsubjtravelerprofiles.emailaddress,
       tblbookeditems.confnumber,
       tblitinreimburements.reimbitemid,
       tblitinreimburements.travelitemid_fk,
       tblitinreimburements.dateofexp,
       tblitinreimburements.description,
       tblitinreimburements.units,
       tblitinreimburements.unitcost,
       [units] * [unitcost] AS Total,
       tblitinreimburements.requested,
       tblitinreimburements.sent
FROM   tblstudies
       INNER JOIN (((tblsubjtravelerprofiles
                     INNER JOIN tblcountries
                             ON tblsubjtravelerprofiles.countryid_fk =
                                tblcountries.countryid)
                    INNER JOIN tblcountryregions
                            ON tblsubjtravelerprofiles.regionid_fk =
                               tblcountryregions.regionid)
                   INNER JOIN (((tblstudyvisitschedule
                                 INNER JOIN tblsubjectbookings
                                         ON tblstudyvisitschedule.studyvisitid =
                                            tblsubjectbookings.studyvisitid_fk)
                                INNER JOIN tblbookeditems
                                        ON tblsubjectbookings.subjbookingid =
                                           tblbookeditems.subjbookingid_fk)
                               INNER JOIN tblitinreimburements
                                       ON tblbookeditems.travelitemid =
                                          tblitinreimburements.travelitemid_fk)
                           ON tblsubjtravelerprofiles.travelerid =
                              tblsubjectbookings.travelerid_fk)
               ON tblstudies.studyid = tblstudyvisitschedule.studyid_fk
WHERE  ( ( ( tblsubjtravelerprofiles.subjid_fk ) =
                    [reports] ! [rptunwrittenchecks] ! [txtsubjectid] )
         AND ( ( tblitinreimburements.dateofexp ) <= Now() )
         AND ( ( tblitinreimburements.unitcost ) <> 0 )
         AND ( ( tblitinreimburements.sent ) IS NULL ) )
ORDER  BY tblitinreimburements.dateofexp,
          tblitinreimburements.description;

New code
Code:
SELECT tblsubjtravelerprofiles.subjid_fk,
       tblstudies.nickname,
       tblsubjectbookings.subjbookingid,
       tblsubjtravelerprofiles.firstname,
       tblsubjtravelerprofiles.lastname,
       tblsubjectaddresses.primary,
       [address1] & Iif([address2] <> "", ";" & [address2], "") AS PrimAddress,
       tblsubjectaddresses.city,
       tblsubjectaddresses.postcode,
       tblcountryregions.displayedregion,
       tblcountries.countryabbreviation,
       tblsubjtravelerprofiles.emailaddress,
       tblbookeditems.confnumber,
       tblitinreimburements.reimbitemid,
       tblitinreimburements.travelitemid_fk,
       tblitinreimburements.dateofexp,
       tblitinreimburements.description,
       tblitinreimburements.units,
       tblitinreimburements.unitcost,
       [units] * [unitcost]                                      AS Total,
       tblitinreimburements.requested,
       tblitinreimburements.sent
FROM   ((tblsubjects
         INNER JOIN tblsubjtravelerprofiles
                 ON tblsubjects.subjectid = tblsubjtravelerprofiles.subjid_fk)
        INNER JOIN (((tblstudyvisitschedule
                      INNER JOIN tblstudies
                              ON tblstudyvisitschedule.studyid_fk =
                                 tblstudies.studyid)
                     INNER JOIN tblsubjectbookings
                             ON tblstudyvisitschedule.studyvisitid =
                                tblsubjectbookings.studyvisitid_fk)
                    INNER JOIN (tblbookeditems
                                INNER JOIN tblitinreimburements
                                        ON tblbookeditems.travelitemid =
                                           tblitinreimburements.travelitemid_fk)
                            ON tblsubjectbookings.subjbookingid =
                               tblbookeditems.subjbookingid_fk)
                ON tblsubjtravelerprofiles.travelerid =
                   tblsubjectbookings.travelerid_fk)
       INNER JOIN ((tblsubjectaddresses
                    INNER JOIN tblcountryregions
                            ON tblsubjectaddresses.regionid_fk =
                               tblcountryregions.regionid)
                   INNER JOIN tblcountries
                           ON tblsubjectaddresses.countryid_fk =
       tblcountries.countryid)
               ON tblsubjects.subjectid = tblsubjectaddresses.travelerid_fk
WHERE  ( ( ( tblsubjtravelerprofiles.subjid_fk ) =
                    [reports] ! [rptunwrittenchecks] ! [txtsubjectid] )
         AND ( ( tblsubjectaddresses.primary ) = true )
         AND ( ( tblitinreimburements.dateofexp ) <= Now() )
         AND ( ( tblitinreimburements.unitcost ) <> 0 )
         AND ( ( tblitinreimburements.sent ) IS NULL ) )
ORDER  BY tblitinreimburements.dateofexp,
          tblitinreimburements.description;

I think this is the difference:
Code:
INNER JOIN ((tblsubjectaddresses

                    INNER JOIN tblcountryregions

                            ON tblsubjectaddresses.regionid_fk =

                               tblcountryregions.regionid)

                   INNER JOIN tblcountries

                           ON tblsubjectaddresses.countryid_fk =

       tblcountries.countryid)

               ON tblsubjects.subjectid = tblsubjectaddresses.travelerid_fk
 
Last edited:

Micron

AWF VIP
Local time
Today, 11:54
Joined
Oct 20, 2018
Messages
3,476
I also saw nothing obvious, although I see that the bracketing of joins is different. In the past I've seen queries that posed problems because of this, but I can't recall if that made them read only. In cases like this, I'd avoid any form that you might be using and start with a query copy. In design view, remove one field at a time until it works. That is not 100% definitive as the last removed field may be the singular cause or it may have been a combination of that and some other field. In those cases, I'd go back to the beginning and remove only that field. If the issue goes away, it is that singular field. If it comes back it is that field plus one or more others. It can be like looking for a needle in a haystack.

Once you know which field(s) is/are responsible you have something you can work with. I have long been under the impression that queries containing calculated fields were, for the most part not editable yet you indicate that in your case, one or more calculated fields work. Perhaps you have set criteria or a join on such a field, which is something I'd expect an issue with. 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?
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
If you are only updating one table, it is also worth checking how your joins are done, it might be by removing a join and using criteria instead will solve the problem. So perhaps try this as criteria tblSubjects.SubjectID = tblSubjectAddresses.TravelerID_FK
I'm not sure what you mean by using criteria instead of a join for the addresses. The table was added because I needed data from it for my recordset, but that table isn't edited. I'm a self-taught, moderate user, so my apologies for not understanding your comment. I've been learning by trial-and-error and the "google-it" method.

The other thing you can check is the query (and subsequent form) has the recordset type set to dynaset - inconsistent updates

The recordset is dynaset, but I'll try dynaset - inconsistent updates, just for fun.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
Pretty unreadable. Use a SQL formatter. Put my money is that one of those inner joins is not on a PK.

Sorry, I just copy and pasted from Access' SQL view. I didn't know there was a SQL formatter. It could be that one of my joins isn't a PK, but I looked and couldn't identify one that wasn't.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:54
Joined
May 21, 2018
Messages
8,463
Here is a good list of why things are not updateable

Out of that list the only thing I could see would be the possibility of a join on a non-indexed field. I do not see any of the other cases.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
Out of that list the only thing I could see would be the possibility of a join on a non-indexed field. I do not see any of the other cases.

Makes sense. I just couldn't find it.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
In design view, remove one field at a time until it works.

This is what I did to find that the tblSubjectAddresses join was the issue. I had removed the tables joined to that table (Countries and Regions) and that didn't fix the issue. Once I removed that join, I was able to edit the field in datasheet view.

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?

Not that I found, but maybe I'm missing something or am wrong my interpretation of "indexing."
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:54
Joined
May 21, 2018
Messages
8,463
Makes sense. I just couldn't find it
Nothing stands out to me. I edited my post to show the new and old code and what I think is different. My only guess could be what I said about indices. You do not having anything else I could see.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:54
Joined
May 21, 2018
Messages
8,463
This is what I did to find that the tblSubjectAddresses join was the issue. I had removed the tables joined to that table (Countries and Regions) and that didn't fix the issue. Once I removed that join, I was able to edit the field in datasheet view
So what is wrong with it? You already knew that was the problem because that is what was new from the original query.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
So what is wrong with it? You already knew that was the problem because that is what was new from the original query.

Per my original post, the problem was: I used to have one query that I used to create a recordset to generate an email and then could UPDATE a field in that recordset to show the email had been sent. When I added in the address join (that is now needed in order to get an address as part of the email) it made the recordset read-only, so that this code no longer works:
Code:
... everything to create the recordset and generate the email

    With rs
        .MoveFirst
        Do Until .EOF
            .Edit
            !Requested = Date
            If IsNull(!ConfNumber) Then !ConfNumber = "REQ"
            .Update
            .MoveNext
        Loop
        .Close
    End With

... close out the Sub

As stated, 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? Maybe I don't understand how to check indices.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
The other thing you can check is the query (and subsequent form) has the recordset type set to dynaset - inconsistent updates

So, interesting development: Changing the query from Dynaset to Dynaset (Inconsistent Update) let me alter the field in datasheet view, but still gave me the 3027 runtime-error when trying to update the Recordset via the Sub.

It's demoralizing to discover how little you know about things.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 28, 2001
Messages
27,005
I'm going to take a wild-eyed guess on this one. Typically, when you want to do an update, you cannot have any ambiguity in what is being updated. You said this problem started when you gained the option of having more than one record to be updated because of having more than one address. This is hard to explain, but it sounds like for some reason Access wants to update only one record but the query thinks that your selection criteria would specify more than one record to be updated. You said that you would remove the addresses after the mail was sent and it is fighting with you because it doesn't know which address to remove. Rather than diddle with the JOIN, diddle with the WHERE clause. Try to find a way to specify which address record is being updated.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
You said this problem started when you gained the option of having more than one record to be updated because of having more than one address.

So the address isn't getting updated. The purposed of the activity driven by the Sub is to send an email to someone with instructions and documentation on reimbursing some expenses. The email includes: who, what, where, why and how. Every single one of the fields in the Query are required to either:
  1. To define the criteria of which records to extract for the email (who's getting paid, how much and what for.)
  2. Build the email that is being sent
  3. Update the associated expense records to show that payment has been requested (so that it doesn't get requested again.)
In prior projects, there was only ever one address for the recipient, and those fields were part of their profile table. With this update, the recipient might have multiple addresses, but payments are only sent to one. Employing what I believe to be good database design, I created a new table for the address fields to allow a one-to-many relationship for possible addresses.

So, the only difference is that now the address data comes from a linked table. That's it. No addresses are being updated or manipulated.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 28, 2001
Messages
27,005
It is not significant that an address is or isn't being updated. It is that the JOIN includes an address that came from a (child-based) list and the update doesn't qualify the JOIN to a unique record. Even if there is nothing to write back in that part of the JOIN that contains the address, if there is ANY ambiguity, Access gets confused.

The success of writing a separate query simply means that you removed the ambiguity in that separate query.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
That makes sense. I guess I'm just not going to understand why this particular JOIN brought in some ambiguity and how to avoid it in the future when building other queries. In looking at the query in design view, the only thing that strikes me is that I've got tblSubjectBookings.TravelerID_Fk as a JOIN to tblSubjTravelerProfiles.TravelerID, and then I use the "misnomer" tblSubjectAddresses.TravelerID_FK as the JOIN to tblSubjects.SubjectID. So I'm using the field name TravelerID_FK for two different joins from different tables. Just to set my mind straight, I'm going to rename the field from tblSubjectAdresses to SubjectID_FK and see if that makes a difference, for any illogical (to me) reason.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 10:54
Joined
Feb 28, 2001
Messages
27,005
OK, let's try it a different way. This is totally contrived and simplified to the point that I might have oversimplified it. You want to do an update that involves a JOINed pair of tables.

Code:
UPDATE A INNER JOIN B ON A.NDX = B.NDX SET A.WHATSIT = XYZ WHERE A.WHYZIT=SOMETHING AND B.HOWZIT=SOMETHINGELSE ;

Now let's say that in table B, the particular combination of WHERE criteria lead you to two records via the JOIN. I.e. two child records in B for the single record in table A selected by those criteria. Further, I emphasize that BOTH of the records in B match the WHERE clause. So you are going to update the record in table A that matches this specification.

But you actually AREN'T DOING THAT. What you told Access via that SQL is that you are updating the virtual table A JOIN B (with the ON clause to qualify it). In this case, you didn't discriminate between the two cases of B that matched. So you would normally say, well it should update BOTH records matching the criteria. But remember this is a contrived case I'm describing, AND you are using multiple tables, not just two tables.

So for snorts & giggles, let's say those criteria cause record 10 in table A to match records 19 and 31 in table B. Did you want to update A(10) JOIN B(19) or A(10) JOIN B(31)? The record to be updated is not uniquely specified by those criteria.

You could rightfully say that since you are only updating an element of table A that is the same in either case, it OUGHT to work. Maybe it ought to just update both implied records. But set theory, which is the basis of most database engines these days, doesn't always think like we do. That is an ambiguous update to Access. because you are updating the SET of records A JOIN B and that set is not uniquely qualified. So it won't work. And the other query that you generated for update-only purposes was apparently specific enough to avoid the ambiguity.

In the earlier link to reasons why a query cannot be updated, the example I just gave was due to the listed reason of faulty indexing or faulty selection.
 

Sonnydl

Registered User.
Local time
Today, 08:54
Joined
Jul 3, 2018
Messages
41
You could rightfully say that since you are only updating an element of table A that is the same in either case, it OUGHT to work. Maybe it ought to just update both implied records. But set theory, which is the basis of most database engines these days, doesn't always think like we do. That is an ambiguous update to Access. because you are updating the SET of records A JOIN B and that set is not uniquely qualified. So it won't work. And the other query that you generated for update-only purposes was apparently specific enough to avoid the ambiguity.

ZOOOOOOM!!!! That's the sound of the dbase theory going right over my head. Actually, I get what you are saying theory-wise. The logic of it. What I don't understand, and have given up trying to at this point, is the practical application of why that one additional JOIN is the fly in the ointment, the sabot in the gear, the wall to my face.

I appreciate your attempts to edify me, but at this point, I fear it may be a lost cause. My hope was this had an obvious solution that I was just missing and could avoid in the future. The reality seems to be that it's perhaps beyond my grasp. I've looked at the indexing, and it look fine to me.

Index.png


I tried to clarify the query to remove possible ambiguity in the JOINs by adding the tblSubjects to which the tblSubjectAddresses is linked (see the layout below), to no avail.

Revised.png


The only thing that "fixes" it, the only thing that lets me edit the tblItinReimburements.Requested field in datasheet view (and hence using rs.edit/rs.update to change that field) is removing the tblSubjectAdresses from the query. ¯\_(ツ)_/¯
 

Users who are viewing this thread

Top Bottom