Access front end, SQL Server back end.
Simple update query
PARAMETERS ParamTransactionID Long, ParamVoidFlag Short;
UPDATE tblTransaction
SET tblTransaction.VoidInProgressFlag = [ParamVoidFlag]
WHERE (tblTransaction.TransactionID=[ParamTransactionID]);
using the query here
Set qdef = CurrentDb.QueryDefs("qUPD-tblTransaction_VoidInProgress")
qdef.Parameters![ParamVoidFlag] = VoidFlag
qdef.Parameters![ParamTransactionID] = TransactionID
qdef.Execute dbFailOnError + dbSeeChanges
qdef.Close
gives
[Microsoft][ODBC SQL Server Driver]Query timeout expired
ODBC--update on a linked table 'tblTransaction' failed.
Editing the table directly works.
Opening the query and giving parameters works.
From the app still doesn't.
UPDATE
Deleted the view, no affect.
The old version is now getting the same failure, so it seems like it is not a code issue.
The only thing in common is the table, so it might be a minor change I made there.
I'll check and see if it is just that table or the entire database.
But seems odd that I can make the change by running the query directly, but get different results running it from code.
UPDATE 2
I thought that perhaps it was something to do with the entire database being read-only somehow, and this is just the first place it is getting hit. But no. Other forms can update their tables with no issues.
So it looks related to the specific table. But it still seems odd that I can update perfectly fine by just running the Update Query.
UPDATE 3
To make testing easier, I am running the queryfrom the main menu form, instead of going through all the forms to get to the point where it fails.
Running against the original DB schema worked. Made the same changes again, replacing NTEXT with VARCHAR(MAX), and it still works.
Back to the original table, still works.
Go back through all the forms, fails.
So the problem seems to be related to one of the forms that is open.
I'll go back through that sequence again.
Also, this explains why it works from the query and not from the form.
Sadly, I can't get to the query to run that while the form is open.
It turned out that the query failed if a particular form was open. That form queried the same table, but with Snapshot instead of Dynaset. I don't know why that locks the table. It has a proper key and index. But Dynaset fixes it.