[X]My Assistant

[mike60smart]

Hi

I am trying to run the following Update Code but get the following error:-

 error2.PNG ( 3.48K )Number of downloads: 0


The code is as follows:-

CODE

Private Sub cmdUpdate_Click()

10        On Error GoTo cmdUpdate_Click_Error
          Dim strSQL As String


20      CurrentDb.Execute "UPDATE tblPOItems SET CustomerPOID=" & Me.txtCPOID _
        & " WHERE CustomerPOID Is Null;", dbFailOnError




30    MsgBox "All Parts Inserted", vbInformation

40    [Forms]![frmCustomers].[frmPOItemssubform].[Form].Requery

          
50        On Error GoTo 0
60        Exit Sub

cmdUpdate_Click_Error:

70        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdate_Click, line " & Erl & "."

End Sub

When I hit Debug on the error prompt it highlights these lines:-

CODE

20      CurrentDb.Execute "UPDATE tblPOItems SET CustomerPOID=" & Me.txtCPOID _
        & " WHERE CustomerPOID Is Null;", dbFailOnError

Any help appreciated

[GroverParkGeorge]

This looks like the error introduced with the recent Windows Update, as discussed here.


There is a fix for the MSI version of Access 2016. Others are on the way. Check the links in that announcement and elsewhere.

[mike60smart]

Hi George

Many thanks for the heads-up.

I tried downloading and installing the apparent fix for this but I had no joy.

However, David's suggested fix worked a charm

This was Davis's Fix:-

CODE

          Dim strSQL As String


20      CurrentDb.Execute "UPDATE (SELECT * FROM tblPOItems) SET CustomerPOID=" & Me.txtCPOID _
        & " WHERE CustomerPOID Is Null;", dbFailOnError


30    MsgBox "All Parts Inserted", vbInformation

40    [Forms]![frmCustomers].[frmPOItemssubform].[Form].Requery

Many thanks

[ADezii]

In the meantime, here is a Band Aid:

CODE

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim lngCustPOID As Long

If IsNull(Me![txtCPOID]) Or Not IsNumeric(Me![txtCPOID]) Then Exit Sub

lngCustPOID = Me![txtCPOID]

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("tblPOItems", dbOpenDynaset)

With rst
  Do While Not .EOF
    If IsNull(![CustomerPOID]) Then
      .Edit
        ![CustomerPOID] = lngCustPOID
      .Update
    End If
      .MoveNext
  Loop
End With

rst.Close
Set rst = Nothing

MsgBox "Update Complete!", vbInformation, "Update [CustomerPOID]"

[cheekybuddha]

Can you imagine in several years when we have members thinking that Access must use an even more weird variation of standard SQL than it already does after coming across old db's and examples using this daft hack!!!

[mike60smart]

Hi David

Glad you Experts are available as us not so gifted would be really lost

Thanks again

[FrankRuperto]

@David

In Oracle sqlplus, we would be using a dummy table named "dual" as a workaround. I wonder what motivated MS to publish the Office "security update". Could it be a horror story where corporate databases got hacked with bulk updates by hacker(s) that got a hold of ODBC connection strings and exploited the db's using SQL injections, or hardcoded in vba? It wouldn't necessarily have to be done with Access, as vba can also execute in other Office components like Excel, Word, etc. I have seen SQL injection exploits by entering strings like "drop table customer", "delete from customer where ...", etc. in text fields, so why would MS just restrict direct bulk updates and not also restrict SQL deletes? If a db admin doesnt really lock down permissions in a db server like SQL Server, Oracle, etc then its a sitting duck. As for Access' ACE engine, is there a way to tighten permissions at the table-level other than putting a password on the accdb?
This post has been edited by FrankRuperto: Nov 19 2019, 06:05 PM