How random are randomly generated numbers ?

[Savvoulidis Iordanis]

Hi there. Going straight to the problem. (Access 2000, Jet SP8, WinXP)

I have a table that has a string primary key, consisting of 25 numbers,
which are generated randomly using the following function:
Public Function NUMRandom(lowerbound As Long, upperbound As Long) As Long
NUMRandom = Int((upperbound - lowerbound + 1) * Rnd + lowerbound)
End Function

The function above is called from the following function:
Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer

s = ""

For i = 1 To 25
Randomize
s = s & CStr(NUMRandom(0, 9))
Next

APPCreateCode= s
End Function

After a couple of weeks, this code started producing duplicate key values (I
don't know why, but I got an Access message when trying to insert.) After
this point, whenever the user pressed a button to insert a record, he always
got the duplicate key message. He had to click over twenty times, to get the
record inserted with a random primary key (and sometimes he' d give up
trying). So I changed the code a little as follows:
Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer, j As Integer, li_rnd As Integer
Dim ls_temp As String

s = ""

Randomize
li_rnd = NUMRandom(1, 30)

For i = 1 To 25
For j = 1 To li_rnd
Randomize
ls_temp = CStr(NUMRandom(0, 9))
Next

s = s & ls_temp
Next

APPCreateCode= s
End Function
--------------
What I did, was to execute the randomize function a few more times (and also
for a random number of times in the inside loop. Then I gave the application
back to the client, and everything went well, but only for a couple of
weeks. Then the same problem occured again. Still the user got the duplicate
key message. This is an unexpected access behavior to me and I don't know
what I do wrong.
QUESTIONS:

1) How many characters are compared in the primary by Access, before
displaying the duplicate key message? (a) All of them in the key, or (b)
only a few?

2) If (a), then is there any better way of producing this primary key?

3) Is something wrong with the randomize and rnd functions?

TIA
Savvoulidis Iordanis
Greece

 

[Savvoulidis Iordanis]

Forgot to add that the table till now, has only about 3400 records!!

 

[Douglas J Steele]

What's wrong with using a Autonumber field, set to Random?

You might also try moving the Randomize statement to outside of the loop.
The Randomize statement provides a seed value for the random number
generator. You have the option of passing a number to the statement. If you
omit number, the value returned by the system timer is used as the new seed
value. It's possible that your machine is running so fast that Randomize is
repeating the same seed value. Do your existing numbers have a lot of
repeating digits?

In answer to your first question, for a text field (i.e. <= 255 characters),
all characters in the field must be unique.

 

[Guest]

You really only need to issue the randomize statement once. It is basically
used to set the seed for the rnd function. I would suggest something like
this:

Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer

s = ""
Randomize
For i = 1 To 25
s = s & CStr(Int(Rnd * 10))
Next i

APPCreateCode= s
End Function

Then, to avoid the error and user frustration, I would check the code before
the insert to ensure the number does not already exist in the table. I think
the above function will give you a better spread, but no random number
generation will ever be completely unique:
For example purposes, MyTable will be the name of the table you want to
create the record for and MyKeyField will be the name of the field you want
to put the number in:

Dim strKeyVal as String
Do While True
'Create a string to use for the code
strKeyVal = APPCreateCode
If IsNull(DLookup("[MyKeyField]", "MyTable", [MyKeyField = '" _
& strKeyVal & "'")) Then
Exit Do 'This code is not in the table
End If
'If the DLookup did not return Null, then that string is alread used, try
another
Loop
'At this point strKeyVal can be used to create a new code

 

[Savvoulidis Iordanis]

Well the 25 character key with numbers is a must. Can't be changed.

I moved the randomize statement outside the loop as Klatuu suggested:

Public Function APPCreateCode() As String
Dim s As String
Dim i As Integer

s = ""
Randomize
For i = 1 To 25
s = s & CStr(Int(Rnd * 10))
Next i

APPCreateCode= s
End Function

But the problem got worse at the user's PC. I forgot to say that it is a
Windows 2003 server. This time there is no way to get a non existing key, so
the user quit's trying to insert the record. I'd say that my solution works
better, but not for a long time. What is wrong?
Should I sent the App (or the portion of the app) to anybody in the
newsgroup to checkit out there?
Or should somebody from Microsoft be concerned? I'd be very glad to contact
somebody from there.

Don't know what to do......

 

[Douglas J. Steele]

What about my suggestion just to use the Random option on the AutoNumber
feature?

If that's not appropriate, perhaps you can explain what your actual
requirements are, as opposed to only saying what's not working.

 

[Stefan Hoffmann]

hi,

Well, the customer needs to display a unique 25 digit code on the printout,
which is a lottery ticket. If it wins, somebody gets his money, giving this
code first. So nobody else must have the same number as a code.

Use the Windows API function CoCreateGUID (Creates a GUID, a unique
128-bit integer used for CLSIDs and interface identifiers).

You may use the code snippet from
http://www.aboutvb.de/khw/artikel/khwcreateguid.htm


mfG
--> stefan <--

 

[Savvoulidis Iordanis]

I don't understand how this could help. Could you be more specific? I only
need numbers.

 

[Stefan Hoffmann]

hi,

I don't understand how this could help. Could you be more specific? I only
need numbers.

Integers are numbers (128 bit / 8 bit/byte = 16 byte). Take two of them,
concatenate them, and you'll get an unique 256-bit integer, which has
more then 25 significant digits.

Take the least significant 25 digits, write them in your primary key
field. Collisions may occur, but orders of magnitude lesser then now.

mfG
--> stefan <--

 

[Savvoulidis Iordanis]

Well, the customer needs to display a unique 25 digit code on the printout,
which is a lottery ticket. If it wins, somebody gets his money, giving this
code first. So nobody else must have the same number as a code.