Create algorithm

Henry Vandersteen
Henry Vandersteen used Ask the Experts™
HI everyone,

I have about 1000 6 digit numbers that will always be unique in my data. They represent an employees ID.  Its part of a key index

I dont want others to see this number as is. What would be a good algorithm I could use/create using vb to change the value to something else that people wouldnt recognize and not be able to figure out what the real number is.
It must still be unique among the other numbers, and I would need to ability to use code to reverse the algorithm back to the original number. So I think that leaves out random number generation

I welcome your thoughts

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

see the  Function KeyCode from this link


I think the OP wants 2-way encryption.
Top Expert 2014

* You don't have to display the data on a form or report -- hide the bound control or make that column of a listbox/combobox have zero width

* Create a new table with a replication key (GUID) field as the primary key and the actual employee number data type as another column.  Insert the employee numbers into the new table.  Add this new table to your queries, joining on the employee number, and display the GUID.

* mostly the same as the prior bullet, but you add a new column into each table to contain a GUID.  You update the records to contain the GUID data and then delete the original employee ID column.

What is the context of this problem.  Why hide/encrypt the value in a database where the user has physical access?
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Why do you need to display the number at all?

I assume that your application does not allow users to view tables and queries directly, but only in forms and reports.  If so, then just hide that field on the forms and reports, so users cannot see it (except maybe HR personnel, who may have a need to be able to lookup users by their employee ID).
Top Expert 2014
Since you're looking for an algorithm/formula, here's a simple one.  Multiply the value of the employee number by a prime number and display the hex value.  You can easily reverse this.

Given the range of the employee IDs (100000-999999), I choose the largest prime number that won't cause an overflow condition when multiplied by the prime number -- 2143
Dim lngEmpID As Long
For lngEmpID = 100000 to 1000000 Step 100000
  Debug.Print lngEmpID+1, (lngEmpID+1) * 2143, Hex((lngEmpID+1) * 2143)
  Debug.Print lngEmpID, (lngEmpID+1) * 2143, Hex(lngEmpID * 2143)
  Debug.Print lngEmpID-1, (lngEmpID-1) * 2143, Hex((lngEmpID-1) * 2143)

Open in new window

Here are salient results from the Immediate window:
 999999        2142997857   7FBB8D61
 900001        1928702143   72F5A8BF
 900000        1928700000   72F5A060
 899999        1928697857   72F59801
 800001        1714402143   662FB35F
 800000        1714400000   662FAB00
 799999        1714397857   662FA2A1
 700001        1500102143   5969BDFF
 700000        1500100000   5969B5A0
 699999        1500097857   5969AD41
 600001        1285802143   4CA3C89F
 600000        1285800000   4CA3C040
 599999        1285797857   4CA3B7E1
 500001        1071502143   3FDDD33F
 500000        1071500000   3FDDCAE0
 499999        1071497857   3FDDC281
 400001        857202143    3317DDDF
 400000        857200000    3317D580
 399999        857197857    3317CD21
 300001        642902143    2651E87F
 300000        642900000    2651E020
 299999        642897857    2651D7C1
 200001        428602143    198BF31F
 200000        428600000    198BEAC0
 199999        428597857    198BE261
 100001        214302143    CC5FDBF
 100000        214300000    CC5F560

Open in new window

You could further obfuscate the value using StrReverse(Hex(lngEmpID * 2143))


Thanks for all your comments, I should elaborate more

True I can control who sees what on my forms/reports etc.

I also split my Access DBs into FE and BE. I dont allow anyone access the BE

The concern is, primarily from HR, that even a 'secure' BE which is on the network and must be 'available' to the frontend, the file could be taken by someone.

With some expertise, it could be broken into. The original EmpID must not be available, so by scrambling the original, the number is meaningless.

The data in this backend is populated from another DB in my control, I would then scram the number before I send it to the 'production' DB

Hope this helps explain the situation better.

One area I am looking at now , is everyone has birthdate in the emp tbl data. If I add each digit, take the sum, and multiply the original emp id by that number, looks good an still unique.
Top Expert 2014

If you have some control over the production database, then I would recommend you implement the lookup table there.  The GUID should be the easiest to implement.
Top Expert 2014

Alternatively, you might hash the employee ID on the server and populate the lookup table with those values instead of GUID values.  If you do this, you should salt all the employee ID values with some value.  This will make brute force attacks more difficult.

HashBytes() reference:


Hi aikimark

I am going to implement a solution based on this idea

Top Expert 2014

Post closure note:
You can also use multiple prime number values for your divisors, based on the range.

Prime -- range
2143 ~ 900k
2383 ~ 800k
2683 ~ 700k
3067 ~ 600k
3571 ~ 500k
4289 ~ 400k
5351 ~ 300k
7151 ~ 200k
10733 ~ 100k

where range is the lower value in the employeeID range (e.g. 100k = 100000 - 199999)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial