Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Q and A _ Update

Posted by: mike60smart Nov 19 2019, 03:51 PM

Hi

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



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

Posted by: GroverParkGeorge Nov 19 2019, 04:03 PM

This looks like the error introduced with the recent Windows Update, https://www.UtterAccess.com/forum/index.php?showtopic=2055753


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

Posted by: mike60smart Nov 19 2019, 04:25 PM

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
cheers.gif

Posted by: ADezii Nov 19 2019, 04:31 PM

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]"

Posted by: cheekybuddha Nov 19 2019, 04:31 PM

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!!! crazy.gif pullhair.gif cryhard.gif ohyeah.gif

Posted by: mike60smart Nov 19 2019, 04:43 PM

Hi David

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

Thanks again
cheers.gif

Posted by: FrankRuperto Nov 19 2019, 05:19 PM

@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?