Can Access automatically generate random text/numeric strings?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am setting up a database of customers and I want to assign each customer a
unique password. Can Access automatically generate this for me. I want the
format to be 5 letters followed by 3 numbers? The code needs to be unique to
the customer.
 
Does Access have an "off-the-shelf" function to do what you've specified?
No.

Does Access have functions that YOU can use to create your own function to
do this? Yes.

How are your coding skills?
 
Jeff, my coding skills are not good! Lorraine.

Jeff Boyce said:
Does Access have an "off-the-shelf" function to do what you've specified?
No.

Does Access have functions that YOU can use to create your own function to
do this? Yes.

How are your coding skills?

--
Regards

Jeff Boyce
<Office/Access MVP>
Click to expand...
 
When you are setting up the Data Type of your customer ID, use AutoNumber,
and then go to the General Tab at the bottom of the setup screen, and set New
Values to Random. This will create a large random integer. The other option
is to set the Field Size to Replication ID, which will create a very long
unique string, composed of letter, numbers, and hyphens, enclosed by curly
brackets.

I don't know of a way of creating the format of 5 letters followed by 3
numbers, unless you do that manually for each customer. Doesn't mean there
isn't a way, it's just that I don't know of an easy way of doing it.
 
Perhaps one of the 'group's readers (we're all volunteers) has the time to
create the code. Even if someone else writes it, you'll still need to know
where to ... place ... it, and how to trouble-shoot it if it doesn't work
right away (or "breaks" later on due to unintended consequences).

Do you have some "local talent" you could bring in?

Regards

Jeff Boyce
<Office/Access MVP>
 
Reminder to self: Never, ever, try to answer questions before having second
cup of tea in morning.

I apologize, did not read your original problem correctly. If you don't
mind using Excel, you can generate random letters and numbers there, combine
them into a single string, and then import that into a table of passwords
that you can use. It is not elegant, but it would work. If I wake up enough
in a few hours, I'll see if I can persuade VBA to do the same thing.

columnA: fill with numbers from 1 to whatever, to use as your reference
number in the database
columnB: (This will be your password) =D1&F1&H1&J1&L1&M1&N1&O1
columnC: =RANDBETWEEN(65,90)
columnD: =CHAR(C1)
columnE: =RANDBETWEEN(65,90)
columnF: =CHAR(E1)
columnG: =RANDBETWEEN(65,90)
columnH: =CHAR(G1)
columnI: =RANDBETWEEN(65,90)
columnJ: =CHAR(I1)
columnK: =RANDBETWEEN(65,90)
columnL: =CHAR(K1)
columnM: =RANDBETWEEN(0,9)
columnN: =RANDBETWEEN(0,9)
columnO: =RANDBETWEEN(0,9)

Just copy the formulas from line 1 to however many lines of these you will
need. I combine them in column B to make it easier to import the info into
Access. You'll have this sort of thing in Column B:

BYSUP970
KZUDU358
JOPHO782
MZIHG442
 
Here is some code that is quick and dirty
The "intRandomNumber = Int((26 * Rnd) + 1)" produces a number from 1 to 26
and adds it to the Ascii number that is 1 less than that for "a" (97 is a).
Loop 5 time for the characters and 3 for the number from 0-9.
This has the potential to give you 11 billion different passwords. I
don't now the stats on what happens with the sequence of random numbers that
are generated since this is based on the seed of the system timer, but you
should be ok :)



Function xxxPassword() As String
Dim intRandomNumber As Integer
Dim strPassword As String
Dim intLoop As Integer
Randomize

For intLoop = 1 To 5

intRandomNumber = Int((26 * Rnd) + 1)
strPassword = strPassword & Chr(96 + intRandomNumber)

Next intLoop
For intLoop = 1 To 3

intRandomNumber = (Int((10 * Rnd) + 1)) - 1
strPassword = strPassword & Trim(Str(intRandomNumber))

Next intLoop

xxxPassword = strPassword
End Function


Craig Hornish
 
No matter how unlikely, its possible any routine could generate duplicates,
so after doing the INSERT of a new record you must check for an error
(duplicate key), then you will need to take some action, such as adding one
to the number portion and trying again until you do not get a duplicate error.

Dorian
 
Where are you going to store these passwords? I don't recommend storing them
in an Access table which is not secure. The best password system in my
opinion is one that is generated from the userid that the user enters. These
passwords don't have to be stored at all. All you do is apply a complex
algorithm to the userid to derive the password. If that does not match what
the user enters, they are rejected. The disadvantage is that the user does
not get to choose their own password.

Dorian
 
Back
Top