Random Numbers Repeating

I write some data to an Access database, after which I retrieve the Autonumber that Access generates. Prior to writing the data, it sits in a class and all of the classes sit in a collection. The collection needs a unique string for each entry, so I create one until I can the proper one from Access. Just a place holder that’s unique. Specifically, I use this code:

Public Sub Add(clsRoyaltyLine As CRoyaltyLine)
    If clsRoyaltyLine.RoyaltyLineID = 0 Then
        clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
    End If
    mcolRoyLines.Add clsRoyaltyLine, CStr(clsRoyaltyLine.RoyaltyLineID)
    If Not mobjParent Is Nothing Then
        Set clsRoyaltyLine.Parent = Me.Parent
    End If
End Sub

If this record has already been written to Access or has already been created, it will have a RoyaltyLineID that’s not zero. Otherwise I create a random number between 1 and 99,999 to serve as a unique id for the collection until such time as I can get a proper Autonumber from Access.

After thousands of RoyaltyLines processed, it finally broke. I was entering a particularly large order (the specific invoice line had 66 royalty lines), but only got to line 14 and I got a duplicate. The tenth RoyaltyLineID was the same as the fourteenth. To quote Phillip J. Fry:

Oh, the fools! If only they’d built it with 6,001 hulls! When will they learn?

I guess I’ll increase it by an order of magnitude, but is there a better way?

Posted in Uncategorized

21 thoughts on “Random Numbers Repeating

  1. Thinking in terms of formal database theory, yes, there’s obviously a better way: create these records in a temporary but autokeyed database table rather than in memory. Moral: use built-in features rather than reinvent inferior substitutes.

    Alternatively, if these temporary unique IDs aren’t passed to the final database, then why not create new records with sequential ID numbers? It’s not that difficult to make the last used or next available sequential number persistent.

    Using random numbers as unique keys is bad idea, one that’s pretty much guaranteed to drop your grade a full letter in database courses. Now you’ve seen why, but your response is make the random number larger. Make it as large as you want, but you’re still playing Russian Roulette.

  2. Hmm… random numbers repeating… that is a nasty problem :D
    i am not sure this would help, but instead of assigning a random number if the record is not in accessdb, you can try this,

    If clsRoyaltyLine.RoyaltyLineID = 0 Then
            clsRoyaltyLine.RoyaltyLineID = ACCESS_MAX + ROW_NUM
            ROW_NUM = ROW_NUM+1
    End If

    both access_max and rownum are global numbers that are initiated to 100000 (or some really large number) and 1 before you start the loading process.

  3. Hi Dick –

    Maybe the approach from the RND help–

    “To produce random integers in a given range, use this formula:

    Int((upperbound – lowerbound + 1) * Rnd + lowerbound)

    Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range.”


  4. How about using the system date and time as a “random” number? If you could include “hundredths” of seconds and then maybe add a totally random number, it should be virtually impossible to end up with the same number twice.

  5. Public Sub Add(clsRoyaltyLine As CRoyaltyLine)
    Randomize ‘do not use inside a loop

    If clsRoyaltyLine.RoyaltyLineID = 0 Then
    clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
    End If

    On Error Resume Next ‘duct tape it
    mcolRoyLines.Add clsRoyaltyLine, CStr(clsRoyaltyLine.RoyaltyLineID)
    If Err.Number = 0 Then
    Exit Do
    clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
    DoEvents ‘so the escape key will work
    End If
    On Error GoTo 0

    If Not mobjParent Is Nothing Then
    Set clsRoyaltyLine.Parent = Me.Parent
    End If

    End Sub

    Something similar could also be done in an error handler.

  6. Hi Dick,

    I’m no expert and certainly not in the same league as you and some of the other guys that post here…but have you considered creating an array of say 100 integer values, each of which can be selected randomly when needed and then removed from the array so that the value is no longer available for selection thus avoiding any duplication? For example:

    Sub RandomNum()
            Dim n As Integer, i As Integer
            Dim temp As Integer
            Dim iValues(1 To 100) As Integer
            Dim iDraw(1 To 7) As Integer
            ‘Creates an array of 100 values (1 to 100)’
           For i = 1 To 100
                iValues(i) = i
            ‘Selects 7 unique random numbers between 1 and 100
          i = 0
           For i = 1 To 7
                n = Int(Rnd * (100 – i) + 1)
                iDraw(i) = iValues(n)
                temp = iValues(n)
                iValues(n) = iValues(101 – i)
                iValues(101 – i) = temp
            Next i
    End Sub

    Here we select 7 unique values from a possible 100 values. I wrote this originally in VB and have adapted it for VBA here. Hope this helps.

    Dan Randles

  7. I’ve used a timestamp function in sequel to do this, but NOW() function and the “time” VBA function don’t seem to carry out the milliseconds far enough to create unique values.

  8. I count, which here would be

    If clsRoyaltyLine.RoyaltyLineID = 0 Then
    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1

  9. To generate unique numbers you do *not* need random numbers. Just generate sequential numbers. Or use today’s date + sequential numbers. Or, use Now. Or Timer.

  10. To keep it really simple, why not start at -1 and count backwards(-2, -3,…). Because this negative sequence will never clash with the(positive) autonumbers

  11. Replace Access with SQL Server Express, Int ID with GUID ID, although that is probably not want you want to hear :)

  12. I won’t be calling the database for this number because it would produce too many calls and performance would suffer. I agree that I should be using built-in stuff whenever possible, but the cost is too high in this case. It seems like sequential is the way to go. Not sure why I didn’t think of that.

  13. One problem with sequential: Some of the class members are already in the database and will have a autonumber assigned by Access. If I use a Count to go sequential, it could clash with one of those. So I think I’ll go with

    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1 * -1

    The Count suggestion and the Negative suggestion combined should make it rock solid. Right?

  14. Ok, I’ll take the bait – grin

    clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1 * -1
    Should be…
    clsRoyaltyLine.RoyaltyLineID = (mcolRoyLines.Count + 1) * -1


    This might be more efficient…

    Private Declare Function timeGetTime Lib “winmm.dll”() As Long
    ‘(Returns the number of milliseconds that have elasped since Windows started.)
    clsRoyaltyLine.RoyaltyLineID = timeGetTime

  15. You’re building records outside Access, and you need unique ID fields in your collection outside Access. The records in this collection would eventually be fed to Access. And you’re feeding the temporary ID fields used in your collection in addition to the information fields to the Access table.


    Access supports SQL INSERT statements for a subset of the fields in a table, and as long as you don’t include the temporary ID field from your collection in the INSERT operation into your Access table, Access will generate new unique, NONCLASHING primary keys or autonumber fields for the new records. You may need to dump your collection into a temporary table then insert that table into your main access table, but you’d never have to worry about key clashes again.

  16. DK,

    (mcolRoyLines.Count + 1) * -1 would essentially give you what Dermot suggested, which is sequential negative numbers. This should be rock solid.

    Since I’m lazy, I would simply prefixed the manually generated autonumbering with text; As in Admin438?

  17. Of course, my suggestion would require the field that holds your unique identifier to be text field.

    My granpa always told me (in a scruffy granpa voice), “Good database design prescribes the use of number fields only for values that are to be used in calculations. Stay away from numeric identifiers because you will never add them.”

    Crazy grandpa….

  18. fzz: I don’t send these temporary identifiers into Access. I do use INSERT with every field except RoyaltyLineID which Access dutifully fills in. Here’s the scenario:

    Invoice has one or many InvoiceLines
    InvoiceLines as one or many RoyaltyLines

    When the user selects and invoice, the Invoice, InvoiceLines, RoyaltyLines and other classes are filled from Access. Then the user can add, delete, or edit the Royaltylines. If they add a RoyaltyLine, I don’t immediately write that to the database, only when they ‘Save’. But at this point, my RoyaltyLines collection contains both Access records and non-Access records. That means there’s the potential for a clash between Access’ autonumber and my temporary one.

Leave a Reply

Your email address will not be published. Required fields are marked *