We often ask poster to upload a copy of their db and the issue is often that it contains sensitive data. Here's a little function that we could point them to whereby they could,
ON A COPY OF THEIR DB, randomize string field data. Sometimes complete data removal will not allow us to troubleshoot OP's issue because queries, forms, etc. need records in order to observe or trouble shoot the problem. The following function could solve the issue of revealing sensitive data by randomizing all or part of a string field by calling the function in an update query. The function call goes into the UPDATE TO row of the query design grid. This sql worked properly on a table that I tested it on (I have aliased the table and field names):
Code:
UPDATE tbl1 SET tbl1.field1 = randomizedata(tbl1.field1,3);
The supposition is that it may be desirable to retain n characters at the beginning of the string and randomize the rest, hence the numeric parameter. If not, use 0 (untested). The function call would go into each text field that you want to randomize, using the proper field reference of course. The function being called is
Code:
Function RandomizeData(strField As String, i As Integer) As String
Dim str1 As String, str2 As String
'i= count of characters from beginning of string that are to be retained. Pass to Left function and assign to str1
str1 = Left(strField, i)
'make loop counter start at position number of NEXT character which is to be replaced. End value is string length.
For i = i + 1 To Len(strField)
'make str2 equal current value + a random lower case character from a to z and repeat to end of counter
'to ignore spaces, comment out the If, Else, End If lines
If Mid(strField, i, 1) = " " Then
str2 = str2 + Mid(strField, i, 1)
Else
str2 = str2 & Chr((121 - 97 + 1) * Rnd + 97)
'Debug.Print str2
End If
Next
'piece left and right parts together and return to query
RandomizeData = str1 + str2
'e.g input string = Donald Duck, output string with 1st 2 characters retained might be Dosnoh itat
End Function
If there is any interest in taking this further I can write an additional functions that retain OR randomizes numeric data, retains numeric data in strings (e.g. addresses) or separators (e.g. "-" in phone numbers). I'll put that on hold until I see if there is even any interest in this so far.
***NOTE***
Randomizing data is only practical IF the values are NOT PK (Primary Key) values, or if they are, Cascade Updates has been set in relationships between a field to be randomized and any other table where the values exist as Foreign Keys. If related foreign values are autonumber PK values, then there should be no issue, otherwise randomizing one side of a relationship and not the other would be pointless.