Using db.execute in Access 2019 (1 Viewer)

MarionD · Dec 3, 2019

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 · Dec 3, 2019

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 · Dec 3, 2019

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 · Dec 3, 2019

MarionD said:
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 · Dec 3, 2019

Hi updates say I up to date

So frustrating - also compiles with no errors

MarionD · Dec 3, 2019

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 · Dec 3, 2019

MarionD said:
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:

Code:

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 · Dec 3, 2019

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

theDBguy · Dec 3, 2019

MarionD said:
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 · Dec 3, 2019

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!

Micron · Dec 3, 2019

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 · Dec 3, 2019

MarionD said:
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 · Dec 3, 2019

Thank you!! Magic! the fix works!!

theDBguy · Dec 3, 2019

MarionD said:
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 · Dec 3, 2019

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.

Micron · Dec 3, 2019

vbFailOnError actually is because it is a VBA constant

Thanks for the support!

Using db.execute in Access 2019 (1 Viewer)

MarionD

Registered User.

theDBguy

I’m here to help

MarionD

Registered User.

theDBguy

I’m here to help

MarionD

Registered User.

MarionD

Registered User.

theDBguy

I’m here to help

MarionD

Registered User.

Attachments

theDBguy

I’m here to help

MarionD

Registered User.

Micron

AWF VIP

theDBguy

I’m here to help

MarionD

Registered User.

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

Micron

AWF VIP

Similar threads

Users who are viewing this thread