Sorry folks, Access World will not accept my posts (8 Viewers)

[DakotaRidge]

The following code clears all fields in all tables in the datbase that are identified as sensitive in the description field. If the field is required it alerts you that it cannot clear it.

Private Sub cmdDelSensitive_Click()
  Dim rtn As Long
  Me.txtOut = Null
  rtn = MsgBox("This will delete all sensitive data from your DB. Make sure you have a backup before attempting. Do you want to continue?", vbCritical + vbYesNo, "Delete Data")
  If rtn = vbYes Then
    ClearSensitive
  End If
 
End Sub

Public Sub ClearSensitive()
  Dim tdf As TableDef
  Dim rs As Recordset
  Dim fld As Field
  Dim strSql As String
  Dim strOut As String
  For Each tdf In CurrentDb.TableDefs
   
    If Left(tdf.Name, 1) <> "~" And Left(tdf.Name, 4) <> "MSYS" And Left(tdf.Name, 4) <> "Copy" Then
      strOut = strOut & "TABLE: " & tdf.Name & vbCrLf
      Set rs = CurrentDb.OpenRecordset(tdf.Name, dbOpenDynaset)
      For Each fld In rs.Fields
        If DoesFieldPropertyExist(fld, "Description") Then
          If Left(fld.Properties("Description"), Len("Sensitive")) = "Sensitive" Then
              If fld.Required = True Then
                MsgBox "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required.", vbCritical
                strOut = strOut & "Cannot clear " & fld.Name & " in " & tdf.Name & " It is required." & vbCrLf
               Else
                 strOut = strOut & "-- Clearing Sensitive Field: " & fld.Name & vbCrLf
                 strSql = "Update " & tdf.Name & " Set " & fld.Name & " = Null"
                 'strOut = strOut & "----" & strSql & vbCrLf
                 CurrentDb.Execute strSql
               End If
          End If
        End If
      Next fld
      strOut = strOut & vbCrLf
    End If
   
  Next tdf
  Me.txtOut = strOut
End Sub
Function DoesFieldPropertyExist(fld As DAO.Field, propName As String) As Boolean
    On Error GoTo ErrorHandler
    Dim p As DAO.Property

    ' Attempt to access a standard property (e.g., DataUpdatable)
    ' This will generate an error if the property doesn't exist for this field type
    Dim temp As Variant
    temp = fld.Properties(propName).Value
    DoesFieldPropertyExist = True
    Exit Function

ErrorHandler:
    ' If an error occurred, it means the property was not found directly
    ' Now, check for user-defined properties
    For Each p In fld.Properties
        If p.Name = propName Then
            DoesFieldPropertyExist = True
            Exit Function
        End If
    Next p

    DoesFieldPropertyExist = False ' Property not found
End Function

The tables starting with Copy still have data in those fields. To run this again Copy the copies and rename those so they do not start with the word copy. Or add some tables and mark certain fields sensitve.

Pete, you forced me to look up your full name in one of Colin's YouTube posts. I don't like using a person's code without crediting him or her in my database. Fortunately, I watched one of your recent presentations to Access Europe that Colin posted. Thanks again.

 

[MajP]

Pete, you forced me to look up your full name in one of Colin's YouTube posts. I don't like using a person's code without crediting him or her in my database. Fortunately, I watched one of your recent presentations to Access Europe that Colin posted. Thanks again.

I post a lot of code as examples. It is not fully tested or error checked. You definitely want to test this thoroughly.

 

[DakotaRidge]

Now I understand what AWF wants to prevent with its rule. I am seeing all sorts of scam on this site. I may have to cut the cord.

 

[Mark_]

There's only one thing that I will never implement, and that's splitting a database into a FE and a BE. I have a friend who will do things like that for me. I just can't get my head around doing it.

Best way to "Wrap your head around it" is to think of programs like Notepad. Notepad lets you open another file, edit it, save it. Notepad can create a new text document. Notepad DOES NOT save the data in itself, so if you put in a new copy of Notepad you don't loose everything you've written down.

ACCESS does the same thing. The program ACCESS reads from and writes too your .accdb file. If you get a new copy of ACCESS it doesn't get rid of all of your existing databases. If you don't split, your end users will get really tired of having to put everything back in each time you send out an update. If you NEVER plan to update this may not be a problem, but your description is you constantly make changes.

 

[MajP]

@DakotaRidge,
I read the requirement wrong. I assumed you wanted to clear certain fields, not certain records. So that solution will not help.
If you are deleting records, let me do a new demo. Disregard that.