Scramble Data in Code

Q (from Me): I have Access 2000 and a database I developed for a company. I want to make a "demo" version of this database and put it on my website as an example of what I'm capable of designing. It would be in MDE file, unsecured.

The concern is that there are data contained in the database which MIGHT be of some sensitivity to outsiders, I don't know. But the database needs "sample" data for it to work.

So the question becomes: is there a quick way to "dirty" the data so that it's not sensitive? You know, a quick way to change the names to "John Doe" but without ALL of them saying "John Doe" so that the data still makes sense.

Or is it better to delete every last bit of data and type in my own 'sample' data, even though this itself could take awhile?

Tips?

LRH

A (from newsgroup posting, paraphrased). Here is some sample code for doing this. This is from the newsgroup microsoft.public.access.security, dated April 29th 2003 entitled Making Demo Databases With Sensitive Data.

Sample #1, Scrambling Names With !! Characters

Function cleannames()
Dim db As Database, rs As Recordset, s As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblName")
With rs
   While Not .EOF
        ' prepare to edit the record.
        .Edit
        ' get the person forname into a string.
        s = ![field_name]
        ' obfuscate the content of that string.
        s = Left$(s, 2) & "!!!" & right$(s, 2)
        ' update the field.
        ![field_name] = s
        '(obfuscate other fields in here)
        '. save the updates.
        .Update
        ' move to the next record.
        .MoveNext
   Wend
End With
Set rs = Nothing
Set db = Nothing

End Function

This example took the 1st 2 letters of a person's name, added a few "!!!" characters, and then tacked on the last 2 letters of the person's last name. So "Lorenzo" would become "Lo!!!zo" or "Carol" would become "Ca!!!ol"

Note: to prevent a "type mismatch" error in the "Set Rs" line, I had to disable Microsoft ActiveX Data Objects 2.1 Library under "Tools...References."

Sample #2, Scramble First/Last Names

This code parces out first and last names even when they're in the same field, and it replaces "Larry Harrison" with "Ladley Hansdale." (The 1st 2 letters of the 1st name--La--with "dley" tacked on, and the 1st 2 letters of the last name--Ha--with "nsdale" tacked on.) This code also error-traps for "invalid use of null" error messages. Note: On Error Resume Next doesn't work well in this scenario, it causes it to use the last working entry over and over again.

Function cleannames()
On Error GoTo Err_Command18_Click
Dim db As Database, rs As Recordset, s As String, i As Integer, l As Integer
Dim z1 As String, z2 As String
Set db = CurrentDb()
Set rs = db.OpenRecordset("tblName")
With rs
   While Not .EOF
        l = 0
        ' prepare to edit the record.
        .Edit
        ' get the person forname into a string.
        s = ![field_name]
        ' obfuscate the content of that string.
        i = InStr(s, " ")
        l = Len(s)
        z1 = Left(s, 2) & "dley"
        z2 = right(s, l - i)
        z2 = Left(z2, 2) & "nsdale"
        ' update the field.
        ![field_name] = z1 & " " & z2
        '(obfuscate other fields in here)
        '. save the updates.
        .Update
        ' move to the next record.
        98    .MoveNext
  Wend
End With
Set rs = Nothing
Set db = Nothing

Exit_Command18_Click:
    Exit Function

Err_Command18_Click:
    'Error 94 is the "Invalid Use of Null" Error Message
    If err.Number = 94 Then
        Resume 98
    End If
MsgBox err.description

Resume Exit_Command18_Click

End Function