UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update, Access 2016    
 
   
mike60smart
post Nov 19 2019, 03:51 PM
Post#1


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi

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

Attached File  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

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
GroverParkGeorge
post Nov 19 2019, 04:03 PM
Post#2


UA Admin
Posts: 36,199
Joined: 20-June 02
From: Newcastle, WA


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.

--------------------
My Real Name Is George. Grover Park Consulting is where I did business for 20 years.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
mike60smart
post Nov 19 2019, 04:25 PM
Post#3


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


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

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
ADezii
post Nov 19 2019, 04:31 PM
Post#4



Posts: 2,710
Joined: 4-February 07
From: USA, Florida, Delray Beach


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]"
Go to the top of the page
 
cheekybuddha
post Nov 19 2019, 04:31 PM
Post#5


UtterAccess Moderator
Posts: 11,910
Joined: 6-December 03
From: Telegraph Hill


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

--------------------


Regards,

David Marten
Go to the top of the page
 
mike60smart
post Nov 19 2019, 04:43 PM
Post#6


UtterAccess VIP
Posts: 13,488
Joined: 6-June 05
From: Dunbar,Scotland


Hi David

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

Thanks again
cheers.gif

--------------------
Hope this helps?

Mike

Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
 
FrankRuperto
post Nov 19 2019, 05:19 PM
Post#7



Posts: 364
Joined: 21-September 14
From: Tampa Bay, Florida, USA


@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

--------------------
Currently supporting pawnbrokers that use my store management system developed with Access 2010 on Windows7. Experienced with Informix and Oracle DB's.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    13th December 2019 - 05:01 AM