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:
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?
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.
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,
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.
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.”
…Michael
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.
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
Do
mcolRoyLines.Add clsRoyaltyLine, CStr(clsRoyaltyLine.RoyaltyLineID)
If Err.Number = 0 Then
Exit Do
Else
clsRoyaltyLine.RoyaltyLineID = Int(Rnd * 100000)
Err.Clear
DoEvents ‘so the escape key will work
End If
Loop
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.
How about trapping the error on the .add and just creating another ID?
Oops – Jim beat me to it.
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:
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
Next
‘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
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.
I count, which here would be
If clsRoyaltyLine.RoyaltyLineID = 0 Then
clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1
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.
Here is a possible alternative: rather than unique numbers, use Guids. They are very convenient to use ( myClass.Id = Guid.NewGuid), and have pretty strong guarantees of being unique.
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
Replace Access with SQL Server Express, Int ID with GUID ID, although that is probably not want you want to hear :)
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.
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?
Ok, I’ll take the bait – grin
clsRoyaltyLine.RoyaltyLineID = mcolRoyLines.Count + 1 * -1
Should be…
clsRoyaltyLine.RoyaltyLineID = (mcolRoyLines.Count + 1) * -1
-OR-
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
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.
Why?
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.
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?
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….
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.