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:
and it refers back to this query:
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/
(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;
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#));
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/