Security Query

[access2010]

We are using off premise programers and want to change the data that they receive

Query_Numeric
Change data in the field customer "Credit_Card_No"
from=to 1=3 / 4=6 / 7=9 / 0=2

Query_Alpha
Change data in the field customer "Contact"
from=to A=D / C=F / G=J / K=M / P=S / T=B

Could I please receive a suggestion on how to run both queries.

Thank you

 

[Pat Hartman]

If you can't trust the programmers, perhaps you should consider not outsourcing the project.

Is this a one-time event? You scramble the data and send it off to them for testing. Or is this something that has to happen for current production data? For the former, you would write a couple of functions and in an update query, pass each field you wanted to obfuscate to the appropriate function. If it is the latter, it's a whole different problem. If they have access to the database, there isn't any way to get between them and the tables. You can't force them to use your queries.

 

[nanscombe]

If there is a separate Back End database, containing the data, just create a version complete with a set of dummy data and send them that instead.

 

[MSAccessRookie]

You should be using VBA to do this, but the following (untested) Replace() Function Calls added to your Queries could be a good start for you:

Query_Numeric
 
CLng(Replace(Replace(Replace(Replace(Cstr(Credit_Card_No), "0", "2"), "7", "9"), "4", "6"), "1", "3"))
 
Query_Alpha
 
Replace(Replace(Replace(Replace(Replace(Replace(Contact, "T", "B"), "P", "S"), "K", "M"), "G", "J"), "C", "F"), "A", "D")

I hope I got them right.

-- Rookie