I have an access 2013 datbase with a linked table to SQL Server. My issue is I am joining on this table in an update statement, but due to the no PK contraint I get an error of "The operation must use an updateable query"
Unfortunately, I am unable to modify the DDL/Structure of the SQL table so I am forced to handle this on the Access side of things. I think I am using very basic syntax for my update statement
Code:
DoCmd.RunSQL ("UPDATE ET INNER JOIN [HSICust] ON [ET].[CustNum] = [HSICust].[CustNum] SET [ET].[CustNum] = 'CSP-03231', [ET].[Address] = [HSICust].[Address];")
However, again due to the no PK constraint on the HSICust table the error is thrown. Is the best work-around for this to run a make-table query from the table HSICust and since it is a local table will be writeable, then join on the local table, then delete this table once the Update portion is completed?
Or are there other alternatives that I have not considered?