Link to home
Start Free TrialLog in
Avatar of Henry Vandersteen
Henry VandersteenFlag for Afghanistan

asked on

Create algorithm

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

Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

see the  Function KeyCode from this link

I think the OP wants 2-way encryption.
* 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?
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).
Avatar of aikimark
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Henry Vandersteen


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.
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.
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

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)