Execute UPDATE query in Access not working from VBA

I am running a query in Access via VBA (actually a lot of them), they look more or less like this one:
CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & RequestId
RequestId is basically the ID of the record.
I am able to make it work from the Immediate window  most of the times, sometimes only it does not work in VBA break mode.
I am not able to make it work from the module, at least in most of the times.
At first I thought it is something with quotes (", '), so I experimented with different settings and for a moment it worked in the form showed above, but after a few tries it stopped working.
Then I thought it is something with locked records, but it did not get me anywhere.
I tried using options like dbSeeChanges and dbFailOnError, but it did not help.
I never see any error message from VBA, the record is simply not being updated.
I would be grateful for some insight, since I am currently stuck with this issue.
Access ver. 1904, MS Office 365 ProPlus
Pawel MusialData Specialist (SAP, Winshuttle, Automation)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Neil FlemingConsultant and developerCommented:
Your code seems to work fine.

This may be a silly question, but Is it the case that the update not showing up in an Access form when you click a button? in which case, try adding "me.requery" after executing the update

eg:

Sub test()
Dim itest As Long
itest = 2
CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & itest
Me.Requery
End Sub

Open in new window


Or is it not showing up in the underlying table "product request" either?
Gustav BrockCIOCommented:
Try with:
"UPDATE [PRODUCT REQUEST] SET [Status] = 'Completed with rejects' WHERE ID = " & RequestId & ""

Open in new window

If the ID exists and STATUS is text, it can't fail.
Pawel MusialData Specialist (SAP, Winshuttle, Automation)Author Commented:
Thanks Gustav ad Neil
Unfortuately still nothing.
The database has no forms, buttons or any UI, data is taken from Sharepoint (among others), which serves as data entry point.
Status field Data Type is Short Text
 Product Request Design view Product Request Datasheet viewShort excerpt from the VBA code:
'***MODULE_DETECTION_ERREURS (check)
                'Test validite formulaire
                If check(rs) = False Then
                    url = ERROR_URL
                    Error_msg = Left(Error_msg, 255)
                    CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET [Status] = 'Completed with rejects' WHERE ID = " & RequestId & ""
                    CurrentDb.Execute "UPDATE [PRODUCT REQUEST_SP_DBG] SET [Status] = 'Completed with rejects', Error='" & _
                        Error_msg & "', Details='REQUEST FORM WRONG#" & url & "' WHERE ID = " & RequestId & ""
                    GoTo ending
                End If

Open in new window

Again, there are no errors  from VBA or Access
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

John TsioumprisSoftware & Systems EngineerCommented:
The only case that this UPDATE query would fail it would be if the RequestID is text instead of Numeric.
If this is the case then you should change you code like this
 CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET [Status] = 'Completed with rejects' WHERE ID = '" & RequestId & "'"

Open in new window

If nothing helps..then you just need to do some investigation...just let the application run and after each .Execute just put a breakpoint to see in case it would fail.
Also...a long shot...if this happens on Client's setup then they make sure that VBA content is enabled.
Gustav BrockCIOCommented:
Try using DAO:

Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Select * From [PRODUCT REQUEST] Where ID = " & RequestId & "")
While Not rs.EOF
    If rs!Status.Value <> "Completed with rejects" Then
        Debug.Print "Updates " & rs!Status.Value
        rs.Edit
            rs!Status.Value = "Completed with rejects"
        rs.Update
    End If
    rs.MoveNext
Wend
rs.Close

Open in new window

If errors, it will show which line, and you can easily debug the values.
Hamed NasrRetired IT ProfessionalCommented:
Let us isolate the issue.
Try to recreate the issue in a new database with one table and the update query and the needed module.
If issue persists upload that database.
Pawel MusialData Specialist (SAP, Winshuttle, Automation)Author Commented:
Thanks everybody, at least I got un-stuck after looking at the "CurrentDb.Execute "UPDATE..." for hours;)
There's definitly nothing wrong with that command, it works when I get rid of everything else and simply execute these 2 lines from an ad hoc sub.
There must be something in the code before it, that stops it from working. Unfortunately I still don't know what.
Any suggestions what kind of command can cause such behavior?
Can a record in a table get locked, so that any "CurrentDb.Execute "UPDATE..." fails without any error message?
als315Commented:
Place breakpoins and execute code line by line. If lines with CurrentDB.Execute are executed, save query to text variable, copy resylt to SQL view of query designer, check syntax and try to execute.
Fabrice LambertConsultingCommented:
The execute member function can take an additional parameter, one value will raise an error if the query fail: dbFailOnError.

So use it, write and error handler, put a break-point in the error handler and inspect your variables.
Sample code:
Public Sub test()
On Error Goto Error
    Dim sql As String
    sql = vbNullString
    sql = sql & "UPDATE ……..;"

    Dim db As DAO.Database
    Set db = CurrentDb

    db.Execute sql, dbFailOnError
Exit Sub
Error:
    '// put a break-point here
    Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
End Sub

Open in new window

Side note:
I recommend setting a reference to the object returned by CurrentDb, since this function return a new instance every call.
Pawel MusialData Specialist (SAP, Winshuttle, Automation)Author Commented:
Thanks again for all comments.
I've found the line causing the problem:
Before the above quoted code, another table (Sharepoint list) is being updated via the following code:
Set rs = CurrentDb.OpenRecordset("Product Rquest Material_List_2_DBG")
rs.AddNew
rs("Name") = RequestId
...

Open in new window

My "CurrentDb.Execute "UPDATE [PRODUCT REQUEST]..." stops working exactly after "rs.AddNew"
If I manually (in Immediate window) execute rs.Close, "CurrentDb.Execute "UPDATE [PRODUCT REQUEST]..." works again.
The problem in my VBA module is that rs.Close never happens. There is rs.Update, but only after "CurrentDb.Execute "UPDATE [PRODUCT REQUEST]..."

Does it mean that if a one table is being edited in one Access database via VBA, it has to be first closed so that any other table can be updated? I would understand it if it was concerning one table, but 2 different tables seems to be strange.
The other thing is why there is no message from VBA or Access about locked objects or something like that (I did use dbFailOnError).
Anyone can help me understand that?
Gustav BrockCIOCommented:
You are mixing DAO and CurrentDb.Execute.

Try my clean DAO method listed above.
Pawel MusialData Specialist (SAP, Winshuttle, Automation)Author Commented:
Thanks Gustav.
I have the same result in both DAO and CurrentDb.Execute approach.
This time I have replaced:
CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = 'Completed with rejects' WHERE ID = " & RequestId & ""

Open in new window

with:
                    Dim rs2 As DAO.Recordset
                    Set rs2 = CurrentDb.OpenRecordset("Select * From [PRODUCT REQUEST] Where ID = " & RequestId & "")
                    rs2.Edit
                    rs2!status.Value = "Completed with rejects"
                    rs2.Update

Open in new window

but it only works if I first close the other table/recordset (rs = CurrentDb.OpenRecordset("Product Rquest Material_List_2_DBG")

Maybe this is something obvious, but I'm not an expert, that's why I'm trying to understand it.
If one recordset is still in "editable" state (not Closed and not Updated) I cannot edit another recordset in the same database?
Gustav BrockCIOCommented:
If one recordset is still in "editable" state (not Closed and not Updated) I cannot edit another recordset in the same database?

Not the same record(s). And why would you? Do one edit and update, do the next edit and update.
John TsioumprisSoftware & Systems EngineerCommented:
Have you checked if you by any chance you have the rs variable is declared locally inside the function and globally (in a module)...your mention about the rs object not closing is strange...
Usually a recordset is declared like this
Dim rst as DAO.Recordset
Set rst =....
..... your code .....
rst.Close
Set rst= Nothing

Open in new window

Also ...maybe you have DAO/ADO altogether and you missed to declare explicitly the DAO or ADO nature of the recordset
Lambert HeenanThe ManCommented:
The one thing that jumps out at me in all of this discussion is that nobody is asking the question, "Where and when does RequestID get its value set?"

Going though this thread  chronologically, the only clues that Pawel gives us are that the statement

CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & RequestId

Open in new window



is not working consistently. That is does mostly work when run from the immediate window, but that it often fails to change the data when the actual code module that contains it is run, yet no errors are thrown.

Now I agree with Gustav that his syntactical suggestion is cleaner and reliable...

"UPDATE [PRODUCT REQUEST] SET [Status] = 'Completed with rejects' WHERE ID = " & RequestId & ""

Open in new window


... but still we do not know how RequestID gets its value, and that is the single factor that determines what record is updated. So I'm wondering if RequestID has been set to a value that does not exist in the ID column of the table PRODUCT REQUEST. Zero strikes me as a likely value. The point is that if you run that SQL, and there is no row with the given ID value, no error occurs. It is not an error to fail to update a non-existent row, so using dbFailOnError has no effect.

Gustav then suggested going down the road of opening a new recordset object to edit the data, and the code is fine (though I would make one slight change to the test of the value of Status, from

   
 If rs!Status.Value <> "Completed with rejects" Then

Open in new window

     
      to

   
If rs!Status.Value & "" <> "Completed with rejects" Then

Open in new window

     
which handles the case where Status is Null) but he again assumes that RequestID has a legitimate value.

Then Pawel next gives us a little more of a clue to the problem when he says he has found the cause of the problem, the lines of code before the one we've all been looking at, which operate on another table...

      
Set rs = CurrentDb.OpenRecordset("Product Rquest Material_List_2_DBG")
	rs.AddNew
	rs("Name") = RequestId
	...

Open in new window

     
Still we are not told where RequestId comes from. (I also cannot resist asking why it would be that a table field that has the name "Name" would be assigned the Autonumber value that is apparently stored in RequestID? How is that integer value a 'Name' of some sort?)

Anyway. Then we get another clue. Pawel says that using code to edit the recordset

	Dim rs2 As DAO.Recordset
	Set rs2 = CurrentDb.OpenRecordset("Select * From [PRODUCT REQUEST] Where ID = " & RequestId & "")
	rs2.Edit
	rs2!status.Value = "Completed with rejects"
	rs2.Update

Open in new window

                             
"it only works if I first close the other table/recordset".

And we STILL do not know anything about "RequestId". Show us your code that shows where the value is set. That's what the whole problem hangs on.

And one more thing. Does the code module begin with "Option Explicit"? Could it be that "RequestID" is not the name of a defined variable in the code module?
Neil FlemingConsultant and developerCommented:
Ah, wait.

Now that I think about it, have also experienced some weird behaviour from "currentDB" in my time, and stopped using it. I have some memory that statements were trying to execute before the code had fully retrieved the currentDB object, possibly because Access is creating a whole new connection each time you reference Currentdb (that is speculation, but something strange is going on with it).

The problem was solved by creating a Database object on project open, assigning to CurrentDB, and working with that object instead.

eg:
public myDB as Database

Open in new window


followed by "mydb.execute" etc..

However, I found it easier in the long run to switch entirely to ADODB and away from DAO, using an ADODB connection and ADODB recordsets.

Externally, eg from Excel, I use an ADODB connection to the database. And working inside Access I use an ADODB recordset object hooked to the project connection.

So from Excel:

'Declared at module level
Public AC As ADODB.Connection

Function Connect2DB(Optional DBname As String) As Boolean
'Connect to access db
On Error GoTo errorTrap

If AC Is Nothing Then Set AC = New ADODB.Connection

With AC
If .State = adStateClosed Then
.ConnectionString = "PROVIDER = Microsoft.ACE.OLEDB.12.0;" & _
                    "DATA SOURCE = " & DBname & ";"
.CursorLocation = adUseClient
.Mode = adModeReadWrite
.Open
End If
End With
ConnectDB = True
Exit Function
errorTrap:
MsgBox ("Unable to connect to " & DBname & "." & vbCrLf & "Please check you are connected to the network, that this file exists, and that it is not open for editing.")
ConnectDB = False
End Function

Open in new window


And to execute your query:
If Connect2DB Then
AC.Execute "UPDATE [PRODUCT REQUEST] SET Status = ""Completed with rejects"" WHERE ID = " & RequestId"
End If

Open in new window


From within Access I use:

Public rst As adodb.Recordset

Function OpenRST(sQ As String) As adodb.Recordset
'opens either a pre-configured table or query, or a specific query passed in sQ

'is string passed already a query? Calling IsQuery function below
'If not a query, assume it is a table or query name:
If IsQuery(sQ) Then Else sQ = "SELECT * FROM " & sQ

Set OpenRST = New adodb.Recordset
With OpenRST
        .ActiveConnection = CurrentProject.Connection
        .Source = sQ
        .LockType = adLockOptimistic
        .CursorType = adOpenStatic
        .Open
End With
End Function

Function IsQuery(sQ As String) As Boolean
Dim iSpace As Long, sCheck As String
iSpace = InStr(sQ, " ")
If iSpace > 0 Then
sCheck = UCase(Left(sQ, iSpace - 1))
    Select Case sCheck
        Case "SELECT", "TRANSFORM", "ÏNSERT", "UPDATE": IsQuery = True
    End Select
End If

End Function

Open in new window


The other thing I would try is inserting a "DoEvents" statement between the two lines of Update queries. It may be that Access is attempting to execute the second query while the first is still running. That might explain why it works from the Immediate window, because you are executing one line at a time.

CurrentDb.Execute "UPDATE [PRODUCT REQUEST] SET [Status] = 'Completed with rejects' WHERE ID = " & RequestId & ""
DoEvents
CurrentDb.Execute "UPDATE [PRODUCT REQUEST_SP_DBG] SET [Status] = 'Completed with rejects', Error='" & _

Open in new window


Hope that helps. To try my code you need to add the ADODB dll "Microsoft ActiveX Data Objects 2.7 Library" (I think there is also a 6.0) to Tools/References
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.