Make Query Updatable (1 Viewer)

Djblois

Registered User.
Local time
Today, 07:27
Joined
Jan 26, 2009
Messages
598
I have an access query that i based on 2 tables. One is a table from a 3rd party program, so I cannot modify the table structure. In this table, they do not have a single PK, they use multiple fields as their primary key.


(I tried pasting a screenshot here to show the 3rd party table but it would not show after posting)



In the morning I create a table in my access program so my users can add other information, that is not available in that 3rd party program, with this table I add a foreign key which is PK: [ROUTE_DATE] & "|" & [ROUTE_ID] & '|' & [INTERNAL_STOP_ID]. I don't use Region_ID because it is always the same, it never changes. So the 3 fields together creates a unique identity.



I then am trying to create a form that is based on the 2 separate tables but this form needs to be updateable (I lock the fields from the 3rd party table). I locked the foreign key in my table to be Indexed (No Duplicates), and I have gone through the whole list of reasons why a query may not be updateable, and it, from what I can see, meets the criteria to be.



The reason I cannot download all the data in the morning is, there are fields in the 3rd party database that are updated throughout the day and I need to see it in this screen. Here is my query, what do you think I can do:


Code:
SELECT tblTesting.TestingNoDuplicates, tblTesting.Address, qryStopsInfoInRoadnet.LOCATION_ID
FROM tblTesting LEFT JOIN qryStopsInfoInRoadnet ON tblTesting.TestingNoDuplicates = qryStopsInfoInRoadnet.PK;
and it refers back to this query:

Code:
SELECT TSDBA_DP_STOP.ROUTE_DATE, [ROUTE_DATE] & "|" & [ROUTE_ID] & '|' & [INTERNAL_STOP_ID] AS PK, TSDBA_DP_STOP.LOCATION_ID, [PROJECTED_DEPARTURE]-(5/24) AS PD, [PROJECTED_ARRIVAL]-(5/24) AS PA, [ACTUAL_ARRIVAL]-(5/24) AS AA, [ACTUAL_DEPARTURE]-(5/24) AS AD, [OPEN_TIME]-(5/24) AS OT, [CLOSE_TIME]-(5/24) AS CT
FROM TSDBA_DP_STOP
WHERE (((TSDBA_DP_STOP.ROUTE_DATE)=#2/7/2019#));
Here are the links I found and have been testing:
https://stackoverflow.com/questions/45818379/cant-edit-any-query-based-on-more-than-one-table


https://www.datanumen.com/blogs/15-reasons-sometimes-cannot-edit-data-access-query/
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:27
Joined
Oct 17, 2012
Messages
3,276
Okay, I've done some testing on this for you, and here's what I got to work:

I created one table called ThirdPartyData with the three fields you mentioned above (ROUTE_DATE, ROUTE_ID, and INTERNAL_STOP_ID) created as a primary key, as well as three other fields Stuff1, Stuff2, and Stuff3.

I created a second table called FirstPartyData with An AutoNumber primary key, the same three specific fields ROUTE_DATE, ROUTE_ID, and INTERNAL_STOP_ID, and three other fields Stuff11, Stuff12, and Stuff13.

Then I built a query containing all fields from FirstPartyData except for the Autonumber (which can be included or excluded, it doesn't matter). That query was, of course, updateable.

Then I added ThirdPartyData to the query, and added Stuff1, Stuff2, and Stuff3 to the output. I also left-joined ROUTE_DATE, ROUTE_ID, and INTERNAL_STOP_ID each from 1st Party (representing your internal table) to 3rd Party (representing your vendor table).

Here is the trick, however.

After that was done, I went back to FIRST party data, and added a multi-field index consisting of the three fields ROUTE_DATE, ROUTE_ID, and INTERNAL_STOP_ID. Once I saved the data, closing and reopening the query showed the recordset to be updatable.

Basically, on your local table add the fields you want to join on the vendor table, make SURE that they are the primary key fields and only the primary key fields, and then add an index for those fields. Once that's done, your recordset should be able to be updated. The key here is that you need an index on both sides of the join.

I didn't test with a four field primary with a three key foreign, so it might be the case that you will need to include the fourth field even though it doesn't change.

If you need, I can upload the test database I used for you.
 

Frothingslosh

Premier Pale Stale Ale
Local time
Today, 10:27
Joined
Oct 17, 2012
Messages
3,276
In fact, now that I think about it, you're never going to get it updatable until you include the fourth part of that key (REGION_ID) in your join, because the only way it works is if you join on a unique index.

Think of it like this: For some reason, you have an app where you need to look up some information in an external table based on Address, Apartment, and City. You don't care about State because you only care about one state (let's say Michigan).

The external database, however, identifies addresses based on Address, Apartment, City, and State, because many city names occur in multiple states in the US, and certain street names are VERY common. For example, there are 23 municipalities named Oxford in the US, and I guarantee you that several of them have a Main street. So when you go to look up '400 Main Streeet, Oxford', the external source won't be able to tell WHICH 400 Main Street you want, as there might be 5 of them, and without the state it can't resolve to one record.

The same applies to your situation here - without that Region ID, the 3rd party source cannot resolve to a single record, which in turn causes any queries based on it to be non-updatable, and you're not going to get around that by using a calculated field. By far the easiest and fastest solution you have available to you is to add Region ID to your table, and then join on all 4 fields in the external table's primary key. (You can set the default to whatever your region is and never worry about it again. Just make sure when you first add it that it gets filled out for all existing records.)
 

Users who are viewing this thread

Top Bottom