I know this is long but please bear with me, I need some help.
I am having difficulty updating a value in a table via a series of queries. I am getting the following error: Operation must use an updatable query. (Error 3073)
I have three tables involved, the main table “tblMasterNeeds” supplies criteria, a table joined to the main table by an ID number “tblExistingSegVC” contains the field that must be updated, table “tblCMSforTransGIS” is the data source table and is not linked to the other tables.
tblMasterNeeds contains these fields; ID, HwyNum, BMP, EMP.
tblExistingSegVC contains these fields; ID, HighV/C.
tblCMSforTransGIS contains these fields; HwyNum, BMP, EMP, V/C.
I am using data from three fields; HwyNum, BMP, and EMP in the main table as query criteria to pull data from the data source table, since the data source table does not contain the ID field I am using this query to establish that association.
I then query that query using Max in the Totals row of the V/C field to filter the data down to the record with the maximum value. I now have the highest V/C from the source table associated with the ID field.
I then run an update query that attempts to put the highest V/C in the HighV/C field of table tblExistingSegVC. The two tables are joined in the query by the ID number.
I get the following error Operation must use an updatable query. Which is (Error 3073).
I have accomplished the task by using a make table query to temporarily hold the data and then ran an update query to update tblExistingSegVC from that table but that is clunky and I would like to be able to update the records using queries, code eventually. Since I am able to update the records in this manner I know it is not a data access or permissions issue.
I tried changing the join type but that doesn’t fix it.
Any ideas?
I am having difficulty updating a value in a table via a series of queries. I am getting the following error: Operation must use an updatable query. (Error 3073)
I have three tables involved, the main table “tblMasterNeeds” supplies criteria, a table joined to the main table by an ID number “tblExistingSegVC” contains the field that must be updated, table “tblCMSforTransGIS” is the data source table and is not linked to the other tables.
tblMasterNeeds contains these fields; ID, HwyNum, BMP, EMP.
tblExistingSegVC contains these fields; ID, HighV/C.
tblCMSforTransGIS contains these fields; HwyNum, BMP, EMP, V/C.
I am using data from three fields; HwyNum, BMP, and EMP in the main table as query criteria to pull data from the data source table, since the data source table does not contain the ID field I am using this query to establish that association.
I then query that query using Max in the Totals row of the V/C field to filter the data down to the record with the maximum value. I now have the highest V/C from the source table associated with the ID field.
I then run an update query that attempts to put the highest V/C in the HighV/C field of table tblExistingSegVC. The two tables are joined in the query by the ID number.
I get the following error Operation must use an updatable query. Which is (Error 3073).
I have accomplished the task by using a make table query to temporarily hold the data and then ran an update query to update tblExistingSegVC from that table but that is clunky and I would like to be able to update the records using queries, code eventually. Since I am able to update the records in this manner I know it is not a data access or permissions issue.
I tried changing the join type but that doesn’t fix it.
Any ideas?