Freelance Project Requests Software Development in C# WPF Asp.Net Core Vba Excel Word SQL-Server EF Linq, UWP Net



In Microsoft Access, Random, Anonymous, Pseudonym, Easy conversion to functions. ID number without hash in  vba


In the following demo database, important data is anonymized and pseudonymized.

This means that for important numbers, non-traceable other numbers are inserted,

And pseudonyms are awarded for texts


Here is an anonymized and pseudonymized table



Apply ingthe Random function in a table or query

A one-time random number changes the numbers so that they cannot be traced.

This function is possible as soon as the public function has been inserted in the vba  code page    as follows.



The random number can be created manually via a form and checked for modification


The change of the important numbers is carried out by an update / update query

The critical numbers, which should not be returned, are converted by an anonymization function.


Then the numbers can be encrypted uniformly in a query




In pseudonymization, pseudonyms are assigned to each critical text.

So instead of Maier, Müller Huber is assigned either name 123, name 234, name 456 or hare, mouse, elephant.


Update in SQL

UPDATE tblPersonal_Data

SET tblPersonal_Data.Firstname = "Firstname" & [PersonalNumber]

, tblPersonal_Data.LastName = "LastName" & [PersonalNumber]

, tblPersonal_Data.BirthDay = Date(), tblPersonal_Data.ZipCode = Left([PersonalNumber],5)

, tblPersonal_Data.City = "City" & [PersonalNumber]

, tblPersonal_Data.Email = "Email@" & [PersonalNumber] & ".com";



Vba Code to Create Form Random Number,  init


Vba code to button

Option Compare Database

Option Explicit


Private Sub BtnRandom_Click()

'--------------< BtnRandom_Click()>-------------

'create public Random Number




tbxRandom. Value  =  public_Random_Zahl

'--------------</ BtnRandom_Click()>-------------

End Sub





With the Randomize  class in  vba  Office and the  Rnd(123) function, new random numbers can then be created in vba

The Rnd() commandcreates a  doublenumberof 0-1.

Multiplying a base number then creates the valid number range from 0 to 999.999.999

Then a random number is generated with a vba  function, which generates an offset after a random number between 0 and 999.999.99

Public Function create_Public_Random_Number()

'--------------< create_Public_Random_Number() >--------------


'Init random numbers



'picture random number

Dim lngRandom  As  Long

lngRandom =  Rnd(10000)*  999999  '*a number from 0 to 6


'Assign Public

public_Random_Zahl =  lngRandom

'--------------</ create_Public_Random_Number() >--------------

End Function




Anonymizing numbers with reference functions

With the following public  function in Microsoft Access or Microsoft Excel, you can make numbers anonymize similar to a hash.

The only difference is that random numbers are created, which remain the same for the calculation process in the offset.

Hash functions always produce letters, which cannot then be reused as a number in data tables.

A random number would also not be useful here if external import or export functions have to calculate or reference to doublets or index references.


Option Compare Database

Option Explicit


Public public_Random_Zahl As  Long


Public Function Anonymize_Number(ByVal  sInput As  String)

'--------------< Anonymize_Number() >--------------

'Access via Excel or Access Table as function =Anonymize_Number()

'change an ID number with a random shift


'< check >

If public_Random_Zahl  =  0  Then  create_Public_Random_Number

If sInput  =  ""  Then Exit Function

If Len(sInput)<  3 Then Exit Function

'</ check >


'< convert >

'convert string to number (Excel numbers as string, from SAP)

Dim long_Input  As  Long

long_Input =  CLng(sInput)

'</ convert >


Dim new_Number  As  Long

new_Number =  long_Input  +  public_Random_Zahl


'< return >

Anonymize_Number =  new_Number

'</ return >

'--------------</ Anonymize_Number() >--------------

End Function





For more important higher-level tasks, the conversion function can of course be improved and refined.



Of course, with a lot of effort with a simple change, the reconstruction with criminal means and a lot of effort is possible.

However, an internal change in this way is possible for internal tasks.

The function can, of course, be built more complex, and the data is still not to be used for public use.






Contact for Jobs, Project Requests: