0

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.

BWhite
  • 713
  • 1
  • 7
  • 24
  • I don't have much experience with SQLServer. I have SQLServer Express just to see if I could get it to work. It does. I don't use QueryDefs to update table. Just a query object or in VBA CurrentDb.Execute. – June7 Nov 19 '21 at 01:23
  • It was working with a SELECT and updating the results. That quit working when I added a view. I switched that to the current UPDATE statement. Still doesn't work. I'll try deleting the view tonight and see if that changes anything. – BWhite Nov 19 '21 at 01:47
  • Is it possible that `VoidInProgressFlag` may have a check constraint on it and the value you're passing in via `VoidFlag` does not meet the requirement? – AlwaysLearning Nov 19 '21 at 03:29
  • 1
    You shouldn't name a parameter as a field name (TransactionID). – Gustav Nov 19 '21 at 08:16
  • See here to find the root cause of an ODBC error: https://stackoverflow.com/questions/730414/determine-real-cause-of-odbc-failure-error-3146-with-ms-access – Andre Nov 20 '21 at 16:01
  • Running the query directly works. Running the query from code fails. Which makes me wonder, is this an ADO vs DAO thing? I don't know anything about how Access runs the query, but it is clearly different in some way from when I do it using DAO. – BWhite Nov 23 '21 at 17:00

2 Answers2

1

Ok, the first question is why/how did you wind up with "Short" data type for the parameter?

It should be:

PARAMETERS ParamTransactionID Long, ParamVoidFlag Bit;
UPDATE tblTransaction 
SET tblTransaction.VoidInProgressFlag = [ParamVoidFlag]
WHERE (tblTransaction.TransactionID=[ParamTransactionID]);

You also state that this query works when you run it from the UI.

So, in code, then you have this:

Make sure that ALL code modules have option Explicit at the start like this:

Option Compare Database
Option Explicit

So, your code should now be:

Dim qdef      as DAO.QueryDef

Set qdef = CurrentDb.QueryDefs("qUPD-tblTransaction_VoidInProgress")
qdef.Parameters![ParamVoidFlag] = VoidFlag
qdef.Parameters![ParamTransactionID] = TransactionID

qdef.Execute dbFailOnError + dbSeeChanges

So correct the data type you have for ParmaVoidFlag.

Also, check the table name in the left nave pane - is it

dbo_tblTransaction

or

tblTransaction.

Also, OPEN UP the linked table in design view - (ignore read only message). Look at the data types. You MUST have a PK defined - so check for a PK.

Next up, on sql server side, true/false fields MUST NOT have a default of null. If they do then updates will fail. So in sql server, make sure the table in question has a default of 0 set.

Last but not least? If the query still errors out, then you need to add a timestamp (not date time) to the sql server table and re-link. This will/is required if any columns are floating point.

After you add the option explicit to the start of the code module, then also from menu do a debug->compile - make sure code compiles.

Albert D. Kallal
  • 42,205
  • 3
  • 34
  • 51
0

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.

BWhite
  • 713
  • 1
  • 7
  • 24