none
Query " is corrupt Access bug also impacting ADODB recordset updates? RRS feed

  • Question

  • Hey all,

    I've been bitten in the behind by the recent security patch botch in Access, and I know that a solution is forthcoming for the problems with straight SQL updates with a where clause targeting a table.

    I've found another instance of the same issue, though, and wanted to see if it was generally known. When my program opens a dynamic ADODB recordset, updates values, and attempts to save, things go off the rails with the same error. I assume that under the hood, ADODB is just doing an update query on the database in this case, and hilarity ensues. It's doubly annoying because I can't exactly go in and alter the SQL syntax when it's hidden behind "RS.update".

    Somebody really screwed the pooch on this one.

    Anybody else seen this particular manifestation of this issue?

    thanks!

    Tuesday, November 19, 2019 2:45 AM

All replies

  • I thought I was losing it until I found this. I'm really up a creek unless they fix it.

    If you find a work around please post it when you can.

    Friday, November 22, 2019 2:24 PM
  • A fix has already been released for the installer (MSI) version of Access 2016, for the click-to-run version of Access 2016/2019 and the monthly channel of Access in Office 365. Fixes for Access 2010 and 2013 are expected today (November 22).

    In the meantime, you can uninstall the update that caused the problem. See https://support.office.com/en-us/article/access-error-query-is-corrupt-fad205a5-9fd4-49f1-be83-f21636caedec


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Friday, November 22, 2019 2:57 PM
  • No go on this.  O365 corporate managed, so I can't do anything as far as adding/removing from my user-base systems.  Trying to use .update.  Changed my select's to point to queries and not tables on my recordsource assignment.  Nada.

    UPDATE:  While I was testing the SQL work-around, I discovered that the .update only flags the 'corrupt' message IF the record you are trying to update exist in the recordset.  If it does not exist, it will update just fine.  If the recordset uses the same .source sql query statement for both cases, wouldn't it be corrupt regardless if the record exist or not?

    I also was not able to fully compact and repair the database.  I received a 'could not rename' error.

    SAMPLE CODE:

    ' *** Process User privileges ***
      Set rstTable = New ADODB.Recordset
        
      With rstTable
        Set .ActiveConnection = cn
      ' Punch User in
       .Source = "Select * from z_qry_Punch_IO"
       .LockType = adLockOptimistic
       .CursorType = adOpenKeyset
       .Open
        
      ' Find Current User
        If Not .BOF Then
         .MoveFirst
          If !AD_ID <> Global_USER_ID Then .Find "[AD_ID]='" & Global_USER_ID & "'"  ' User not in first row of table
        End If
        
        If Not .EOF Then
       ' User found in table, process user's punch-in time
         !punch_in = Now()
         !Punch_Out = Null
         !computerID = getComputerName()
        ElseIf .EOF Then
       ' User not found, add user and punch-in time
         .AddNew
         !AD_ID = Global_USER_ID
         !punch_in = Now()
         !Punch_Out = Null
         !computerID = getComputerName()
        End If

     .Update

    **************** SUBSTITUTED SQL Code ****************

        dummy = Nz(DLookup("[AD_ID]", "z_qry_Punch_IO", "[AD_ID]='" & Global_USER_ID & "'"), "NEW")

        If dummy <> "NEW" Then
          DoCmd.RunSQL "Update z_qry_Punch_IO SET z_qry_Punch_IO.ComputerID=' & getcomputername() & " ' z_qry_Punch_IO.punch_in =#" & Now() & "#, z_qry_Punch_IO.Punch_Out=Null where z_qry_Punch_IO.AD_ID='" & Global_USER_ID & "';"
        Else
          DoCmd.RunSQL "Insert Into [z_qry_Punch_IN] ([AD_IN], [ComputerID], [punch_in], [Punch_Out]) values (" & Global_USER_ID & ", " & getComputerName() & ", #" & Now() & "#, " & Null & ");"
          DoCmd.RunSQL "Insert Into z_tbl_Punch_IO ([AD_ID], [ComputerID], [punch_in]) " & _
                       "values ('" & Global_USER_ID & "', '" & getComputerName() & "', #" & Now() & "#);"
        End If

       
    • Edited by wadeness98 Wednesday, January 15, 2020 9:15 PM
    Wednesday, January 15, 2020 5:23 PM