I have a form that displays a list of clients. The form contains 3 combo boxes and others are Text boxes. So when I add the tables to the query which contains the data that goes into the combo box, it doesn't work but when I remove the table the query becomes Updatable. I have attached the images of both the queries.
Updatable Query
SELECT [1-01_Clients_tbl].CNR,
[FNC] & " " & [SNC] AS [Service User Full Name],
[1-01_Clients_tbl].PT AS [Physiotherapist name (adjust)],
[1-01_Clients_tbl].[PDS Score (txt)] AS [Score (txt)],
[1-01_Clients_tbl].[PDS Score (nmbr)] AS [Score (No)],
[1-01_Clients_tbl].[Date for WLI],
[1-01_Clients_tbl].DOB AS [Date of Birth],
[1-01_Clients_tbl].TASCode,
[1-01_Clients_tbl].PTact AS [Active Status Physio Details (adjust)],
[_Circular_Temp].ActiveStatus AS [Active Status APH]
FROM ([1-01_Clients_tbl]
LEFT JOIN [1-01-Clients_TransferXtra_tbl] ON [1-01_Clients_tbl].CNR = [1-01-Clients_TransferXtra_tbl].CNR)
LEFT JOIN _Circular_Temp ON [1-01_Clients_tbl].CNR = [_Circular_Temp].CNR
WHERE ((([1-01_Clients_tbl].CNR)<>1 Or ([1-01_Clients_tbl].CNR)=2)
AND (([_Circular_Temp].ActiveStatus)="yes"));Non-Updatable Query
SELECT [1-01_Clients_tbl].CNR,
[FNC] & " " & [SNC] AS [Service User Full Name],
[1-01_Clients_tbl].PT AS [Physiotherapist name (adjust)],
[1-01_Clients_tbl].[PDS Score (txt)] AS [Score (txt)],
[1-01_Clients_tbl].[PDS Score (nmbr)] AS [Score (No)],
[1-01_Clients_tbl].[Date for WLI],
[1-01_Clients_tbl].DOB AS [Date of Birth],
[1-01_Clients_tbl].TASCode,
[1-01_Clients_tbl].PTact AS [Active Status Physio Details (adjust)],
[_Circular_Temp].ActiveStatus AS [Active Status APH],
[5-10_TeamActiveStatus_Codes_tbl].TextVisible,
[2-01_TeamIDNormalized_tbl].CTeamID
FROM (((([1-01_Clients_tbl]
LEFT JOIN [1-01-Clients_TransferXtra_tbl] ON [1-01_Clients_tbl].CNR = [1-01-Clients_TransferXtra_tbl].CNR)
LEFT JOIN _Circular_Temp ON [1-01_Clients_tbl].CNR = [_Circular_Temp].CNR)
INNER JOIN [5-10_TeamActiveStatus_tbl] ON [1-01_Clients_tbl].CNR = [5-10_TeamActiveStatus_tbl].CNR)
LEFT JOIN [2-01_TeamIDNormalized_tbl] ON [1-01_Clients_tbl].CNR = [2-01_TeamIDNormalized_tbl].CNR)
INNER JOIN [5-10_TeamActiveStatus_Codes_tbl] ON [5-10_TeamActiveStatus_tbl].TeamActiveStatusCode = [5-10_TeamActiveStatus_Codes_tbl].TAScodeID
WHERE ((([1-01_Clients_tbl].CNR)<>1 Or ([1-01_Clients_tbl].CNR)=2)
AND (([_Circular_Temp].ActiveStatus)="yes"));A) Images of Updatable Query
1) The Query
2) Datasheet view of the Query
3) Design view of the form associated with the above query
4) The Actual working form
Note that CTeamID(Team column in the form) and Text Visible(Team Active Status column in the form) is missing as these data is derived from two different tables and that's where the issue starts.
B) Images of Non-Updatable Query
1) The Query
2) Datasheet view of the Query
3) The Form
So here when I added the tables from where we are getting the CTeamID(Team column in the form) and Text Visible(Team Active Status column in the form) data it's now not updatable query.
Any ideas or suggestions as to how to make it working or how to improve the query to make it updatable? Thank you in advance.
1条答案
按热度按时间eyh26e7m1#
Well, it looks like the introduciton of the "inner join" is what blows up this query.
however, I would consider building the query in sql server, and linking as a view.
However, DO KEEP in mind:
Access based tables allow joins and MULTIPLE tables to be updated.
SQL server:
You can have a query (or better a view) that has mutliple tables, but ONLY ONE of the tables can be changed.
In other words, if you edit some columns that belong to MORE then one table, then this is NOT allowed with SQL server tables.
Again:
The view can have multiple tables, but if you edit a column from MORE then one different table, the update does not work with SQL based tables. (it does work with Access based ones).
So, unless you can "disable" some controls on that continues form/datasheet to "limit" or to "ensure" that ONLY ONE BASE table behind will become "dirty", then you can't use that interface. This is a limitation of SQL server, and one that does not (did not) exist when using Access tables as the back end.
SQL server due to transactions, and being ATOMIC does NOT allow a query to have more then one table AND ALSO then udpate in one shot/command.
MS-access tables do allow this!
You can in some cases "kluge" this by using a after update event of the text boxes, and do a me.dirty = false (which forces a write of the record). Since you would be "forcing" a write of the data, then NEVER does more then "one" table in the query get dirty.
So, this is a difference in SQL tables vs Access tables. (only ONE table in the view/query behind can EVER become dirty).
So, by forcing the write of the row data in the text box after update, then you can make this work.
I would thus using a continues form (and not a datasheet) for this purpose (and by continues form, I do mean what is called a multiple-items form.
So, while you can/could/probably get that 2nd query to work (you have to change the inner join to a left join), the NEW issue will be that if you allow editing in that row that will result in more then ONE table becoming dirty, then the update will not work.
So, you either:
Have to be sure ONLY one table behind in that query is ever changed.
Or, do the kluge of adding me.dirty = false in after update of each text box.
or, consider having a button that pops up a form, and only has the columns from one base table behind in that query for allowed edits.
So, just keep in mind that after using up a pot of coffee, and say you REALLY do get that query to become up-dateable?
Keep in mind that rule about ONLY ONE of the tables behind can become dirty at any given time. This means/suggests that while editing could occur in that row, only columns from ONE of the tables behind at a time can become dirty.
So, as noted, I would consider converting that client side query into a view, since that's going to help a WHOLE LOT in operation of that query.
And thus in sql manager, you can then right click on the view, and choose edit. Test it. That way you don't have to go back into ms-access when trying to determine if the query can/does allow updates.
Do keep in mind that you MUST answer the prompt when linking the view to enter the row PK id for access. Keep in mind that if you re-link (point the front end) to a different back end from SQL server? Then the PK row value of the linked view can and will be lost. This issue can be dealt with in a separate question/post, but you do need to keep this additional information in mind.