Avatar of Henry Vandersteen
Henry Vandersteen
Flag 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

Microsoft Access.NET Programming

Avatar of undefined
Last Comment

8/22/2022 - Mon
Rey Obrero (Capricorn1)

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?
Your help has saved me hundreds of hours of internet surfing.
Dale Fye

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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.

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:
Henry Vandersteen

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)
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.