Using db.execute in Access 2019

[MarionD]

Good evening all,

In an Access 2016 database I use:
db.Execute ("update loktbl_Locals set [text] = '" & s & "' where bezeichnung = 'mdbPfad'")

In the table loktbl_Locals I have a field named "text" and one named "Bezeichnung". The Variable "s" here holds the path to the Data (back-end database)
Basically I am trying to write the path into the local table - field "text", where the field "Bezeichnung" has the value "mbdPfad", when logging in to the frontend.

This has always worked fine - till access 2019
Error message says the " query is damaged"

Please help!

 

[theDBguy]

Hi. I wonder if this is related to the last bug from MS. Do you have the latest patches available installed on your computer? For example, can you go to File > Account > Update Options > Update Now?

 

[MarionD]

Hi I have only just installed the Microsoft Office Professional Plus 2019 in the last couple of minutes. I hope all Patches are there but will try now thanks

 

[theDBguy]

Hi I have only just installed the Microsoft Office Professional Plus 2019 in the last couple of minutes. I hope all Patches are there but will try now thanks

Hi. It may or it may not. It couldn't hurt to try. I am hoping it will help fix the problem. If not, we'll have to start digging somewhere else.

 

[MarionD]

Hi updates say I up to date
So frustrating - also compiles with no errors

 

[MarionD]

I have now included the table name but still makes no difference - can anyone think of a reason why this doesn't work? The value of the variable 2 is correct in the debugger?


db.Execute ("UPDATE Loktbl_LOCALS SET Loktbl_LOCALS.[text] = '" & s & "' WHERE (Loktbl_LOCALS.bezeichnung)='mdbPfad'")

 

[theDBguy]

I have now included the table name but still makes no difference - can anyone think of a reason why this doesn't work? The value of the variable 2 is correct in the debugger?


db.Execute ("UPDATE Loktbl_LOCALS SET Loktbl_LOCALS.[text] = '" & s & "' WHERE (Loktbl_LOCALS.bezeichnung)='mdbPfad'")

Okay, so, if this is not related to the last update bug, then let's see if we can troubleshoot it. First, I would suggest assigning your SQL statement to a variable, so you can examine it. For example:

Dim strSQL As String
strSQL = "UPDATE Loktbl_LOCALS SET..."
Debug.Print strSQL
db.Execute strSQL, dbFailOnError

What does it say in the Immediate Window?

 

[MarionD]

If I hold cursor over dbFailonError it says 128 - otherwise see attachment - sorry about the German

 

[theDBguy]

If I hold cursor over dbFailonError it says 128 - otherwise see attachment - sorry about the German

Is error 128 = "query is damaged"?


For example, if you hit F8 or F5, do you get the "query is damaged" error message?


If you copy and paste the SQL statement from the Immediate Window into the Query Grid Designer and try to run it from there, do you get the same error? If so, where does the query designer highlight the SQL statement to point the location of the problem, if it does?

 

[MarionD]

Hi there! yes I do get the error 128 = "query is damaged"? (runtime error 3340) I have just been on google a bit - I found an article (in German) which says the Update on the 12 Nov 2019 caused the problem - and that one should de-install the update as Microsoft has not fixed the bug yet - Dont know if I can do that on a new Installation - will try first and get back to you.
Thankssssss for the help!

 

[Deleted member 147267]

128 is a vb constant for dbFailOnError IIRC. I suspect the actual error is the same as originally reported. I'd still try updating a query rather than the table, just to be sure the fix for 2019 is in place on the pc (or isn't).

 

[theDBguy]

Hi there! yes I do get the error 128 = "query is damaged"? (runtime error 3340) I have just been on google a bit - I found an article (in German) which says the Update on the 12 Nov 2019 caused the problem - and that one should de-install the update as Microsoft has not fixed the bug yet - Dont know if I can do that on a new Installation - will try first and get back to you.
Thankssssss for the help!

Hi. Okay, error 3340 was the one I was referring to earlier, at the beginning of this thread. Sounds like you may have a Volume Licensed copy of 2019, which doesn't have a fix available yet. If so, you can try to apply my temporary fix available for download here. Cheers!

 

[MarionD]

Thank you!! Magic! the fix works!!

 

[theDBguy]

Thank you!! Magic! the fix works!!

Hi. You're welcome. We just have to wait a few more days, and we should have this fixed permanently (crossing fingers). Cheers!

 

[The_Doc_Man]

Just for the record, hovering the cursor over vbFailOnError will ALWAYS return 128 even before you run the query. You don't CARE what vbFailOnError actually is because it is a VBA constant taking from one of the libraries. What you REALLY care about is the value in ERR.NUMBER after running the query. And if there IS no ERR object after running the query, then it ran successfully.

 

[Deleted member 147267]

vbFailOnError actually is because it is a VBA constant

Thanks for the support!