Updateable Query ACC2013

[Michael Abrams]

Based on this query, I need to update the [DelayReason] to “Performance Issue”

I tried changing it to an update query but message “Operation must use an updateable query.

Not sure how to do that.

Code: Select all

SELECT dbo_FullEVTS.RecordNumber, dbo_FullEVTS.TypeOfRequest, dbo_FullEVTS.TAT, dbo_FullEVTS.Finalizer, dbo_FullEVTS.DelayReason, dbo_FullEVTS.DateIn, dbo_FullEVTS.DateCompleted, dbo_FullEVTS.Deleted, dbo_FullEVTS.Outcome
FROM dbo_FullEVTS
WHERE (((dbo_FullEVTS.RecordNumber)>151260) AND ((dbo_FullEVTS.TypeOfRequest) Like "Routine*") AND ((dbo_FullEVTS.TAT)>5) AND ((dbo_FullEVTS.DelayReason) Is Null) AND ((dbo_FullEVTS.Deleted)=False) AND ((dbo_FullEVTS.Outcome) Not Like "p*")) OR (((dbo_FullEVTS.RecordNumber)>151260) AND ((dbo_FullEVTS.TypeOfRequest) Like "URGENT*") AND ((dbo_FullEVTS.TAT)>1) AND ((dbo_FullEVTS.DelayReason) Is Null) AND ((dbo_FullEVTS.Deleted)=False) AND ((dbo_FullEVTS.Outcome) Not Like "p*"));

Thank you for helping

Michael

[HansV]

Does this fail with the same error message?

UPDATE dbo_FullEVTS SET dbo_FullEVTS.DelayReason="Performance Issue"
WHERE dbo_FullEVTS.RecordNumber>151260 AND dbo_FullEVTS.TypeOfRequest Like "Routine*" AND dbo_FullEVTS.TAT>5 AND dbo_FullEVTS.DelayReason Is Null AND dbo_FullEVTS.Deleted=False AND dbo_FullEVTS.Outcome Not Like "p*" OR dbo_FullEVTS.RecordNumber>151260 AND dbo_FullEVTS.TypeOfRequest Like "URGENT*" AND dbo_FullEVTS.TAT>1 AND dbo_FullEVTS.DelayReason Is Nul AND dbo_FullEVTS.Deleted=False AND dbo_FullEVTS.Outcome Not Like "p*"

[Michael Abrams]

Error message:

[HansV]

Ah sorry.

UPDATE dbo_FullEVTS SET dbo_FullEVTS.DelayReason="Performance Issue"
WHERE dbo_FullEVTS.RecordNumber>151260 AND dbo_FullEVTS.TypeOfRequest Like "Routine*" AND dbo_FullEVTS.TAT>5 AND dbo_FullEVTS.DelayReason Is Null AND dbo_FullEVTS.Deleted=False AND dbo_FullEVTS.Outcome Not Like "p*" OR dbo_FullEVTS.RecordNumber>151260 AND dbo_FullEVTS.TypeOfRequest Like "URGENT*" AND dbo_FullEVTS.TAT>1 AND dbo_FullEVTS.DelayReason Is Null AND dbo_FullEVTS.Deleted=False AND dbo_FullEVTS.Outcome Not Like "p*"

[Michael Abrams]

Darn.
The code generates this message:
Operation must use an updateable query

[HansV]

Does your SQL Server table have a unique index (primary key, identity field)?

[Michael Abrams]

dbo_FullEVTS has no primary key

[HansV]

That explains it - a SQL Server table without a unique index cannot be updated from Access. You'll have to add a unique index.

[Michael Abrams]

OK Thanks !