|
 |
access thread: Better way to generate primary keys?
Message #1 by "Tim Nemec" <tim@n...> on Fri, 21 Dec 2001 07:06:11
|
|
In A Nutshell:
I'm looking for suggestions on a better way to handle
the generation of primary key values for a multi-user
MS Access/VBA environment.
The Details:
When a specific event happens, I need to write linked
records to several tables at once. (Thus I need to
know the new primary key value before I write the records.)
I currently use the 'manual increment' approach shown
below (lock and open the system "nextNum" table, get
the value, increment by one and return).
The proc shown below has one twist - if an error occurs,
it returns a large random value (we hope it is unique
but there is guarantee of course). Some sort of failover
plan is needed because the user has just spent several
minutes entering data and we want to do everything possible
to keep from losing it.
The Constraints:
It is far too late to change to change the primary key's
type (Long Integer) to GUID or anything else.
Autonum is not an option because back end maintenance
utilities must update primary key values from time to time.
The Problem:
This procedure works most of the time, but the system is being
run on a slow, heavily loaded system (only 6-7 users of this
app however). I seem to be getting occassional timeouts that
cause the error handler to return a random value. Sometimes
it appears that the timeout value I specified in the
ADO connection object is being ignored - i.e. if the table
is locked by another user, it returns immediately with an error.
The good news is that the data gets saved (using the large
random value). The bad news is that there
is probably a better way to handle this. I need this to
be as 'bullet-proof' as possible. Suggestions?
The Code:
Public Function GetUniqueID() As Long
On Error GoTo Err_GetUniqueID
Dim rs As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim lngId As Long
Dim lngRandom 'Used if all else fails
Set rs = New ADODB.Recordset
Set cnn = New ADODB.Connection
cnn.Open ADOconnstr
cnn.CommandTimeout = 60
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.CursorType = adOpenKeyset
rs.ActiveConnection = cnn
rs.Open "SELECT NxtNum FROM SystemData"
If Not rs.EOF Then
lngId = rs![NxtNum]
rs![NxtNum] = lngId + 1
rs.Update
End If
rs.Close
cnn.Close
Exit_GetUniqueID:
GetUniqueAttemptID = lngId
Exit Function
Err_GetUniqueID:
Err.Clear
If rs.STATE <> adStateClosed Then
rs.Close
End If
cnn.Close
'On error, generate a large random value and pray
Randomize ' Initialize random-number generator.
lngRandom = CLng((1000000 * Rnd) + 8880000)
lngId = lngRandom
Resume Exit_GetUniqueID
End Function
Message #2 by "Rory" <mcneillr@c...> on Sun, 23 Dec 2001 04:30:38
|
|
> In A Nutshell:
>
> I'm looking for suggestions on a better way to handle
> the generation of primary key values for a multi-user
> MS Access/VBA environment.
>
> The Details:
<snip>
Is there any need for the numbers to be sequential? If you don't mind
your key table growing, you could populate it with keys as they are
generated, and check that a new key doesn't already exist. The table,
consisting of only the long KeyID, wouldn't be all that large relative to
the table(s) it is supporting. But this would encounter the same problems!
You may wish to pass the function a table name for it to check against -
it could check the new key is not yet being used.
Finally, with 6-7 users you may be able to avoid going across the network
by preallocating number ranges to each user. You'd need a table local to
each user to coordinate this. A related approach that might work (more
complicated, though) would be to give out a batch (say 10) of numbers each
time the function is called, and to have each user's process track them as
they're used (static array of some sort?). Getting really complicated you
could have a background process checking the number of unallocated
numbers, and getting a new batch before the old one expires - in essence a
buffer process.
Yet one more idea - instead of giving a random number immediately as you
do, you might pause (eg. loop) a few milliseconds and try again, giving
the random number only when you've tried several times. This is doing
some of the OS's work for it, but the users may prefer to wait a bit more
rather than get a number that confuses them.
Hope these ideas might bear fruit for you!
Rory
Rockhampton, Queensland, Australia.
Message #3 by "Tim Nemec" <tim@n...> on Sun, 23 Dec 2001 08:37:48
|
|
Rory:
Thanks for the response. No, I'm not sure the numbers have
to be sequential, just unique and known at the time
I write to the linked tables.
I will give your suggestions some additional consideration
and post more once I've thought this through a little better.
I'm especially interested in the "try again on failure"
approach. In the grand scheme of things it's more important
that this operation succeeds than it is to be quick.
Thanks again for taking the time to reply.
Tim Nemec
Message #4 by "Tim Nemec" <tim@n...> on Mon, 24 Dec 2001 00:50:40
|
|
Rory:
After considering your points, I've come to the following conclusions:
1. My primary key values do NOT need to be sequential, just
unique, long integer, and known in advance. This simplifies
the overall problem
2. I can adapt a version of the 'repeat attempts until
successful" model
3. I can drop the entire 'increment PK by one' approach and eliminate
all pessimistic locking from my application.
Here's how I've now modified my code. When it is time to
write to my linked tables, I:
a. Generate a random long integer (range 100,000,000 to
999,999,999 to use as a primary key.
b. Attempt to insert a 'blank' record using the primary
key value generated above.
c. If I get an error at step (b) I go back to (a) and
repeat until I am successful or until my
ATTEMPT_LIMIT is reached.
d. When step (c) above is successful, I return the
random number used to write the blank record in
step (b) to the calling routine.
e. The calling routine then uses a SQL UPDATE (WHERE
id={the random primary key returned by
CreateEmptyAttemptRecord} ) instead of a SQL
INSERT INTO statement.
The code for the utility routines is shown below. Initial
testing under a 2-user environment looks good but I'll
know more on Wednesday after the full 6-7 people start
using this again.
Thanks for giving me a second set of eyes on this.
Regards,
Tim Nemec
(Note: "gd_NULLLONG" below is a CONST whose value is -1 -- it
isn't really NULL)
Each of the calling routines has this basic structure
Private Function SaveCall() as string
On Error GoTo Err_SaveCall
lngAtID = CreateEmptyAttemptRecord
If lngAtID <> gd_NULLLONG Then
' <do stuff with several tables>
Else 'Attempt to create blank attempt record failed
ErrMsg "Repeated attempts to save your data failed. " & vbCrLf _
& "This may indicate that the MASTER DATABASE is locked open. " &
vbCrLf _
& "Data cannot be saved. CONTACT SYSTEM ADMINISTRATOR"
End If
Exit_SaveCall:
If blnTransInProgress = True Then
If cnn.STATE <> adStateClosed Then
cnn.Close
End If
End If
SaveCall = strResult
Exit Function
Err_SaveCall:
If blnTransInProgress = True Then
cnn.RollbackTrans
cnn.Errors.Clear
End If
LogEvent Ev_Error, "Error while attempting to save...", _
Me.Name, "SaveCall", Err.Number, Err.Description
strResult = "ERROR - Check Event Log"
Err.Clear
Resume Exit_SaveCall
End Function
The utility routines look like this:
Public Function CreateEmptyAttemptRecord() As Long
On Error GoTo Err_CreateEmptyAttemptRecord
Const ATTEMPT_LIMIT = 10
Dim lngResult As Long
Dim blnBlankRecordCreated As Boolean
Dim lngCreationAttempts As Long
Dim lngNewRecID As Long
lngResult = gd_NULLLONG
blnBlankRecordCreated = False
lngCreationAttempts = 0
While (Not blnBlankRecordCreated) And _
(lngCreationAttempts < ATTEMPT_LIMIT)
lngCreationAttempts = lngCreationAttempts + 1
lngNewRecID = GenRandomLong
If InsertBlankAttemptRecord(lngNewRecID) = True Then
lngResult = lngNewRecID
blnBlankRecordCreated = True
Else
'Nothing -- Just go try again
End If
Wend
Exit_CreateEmptyAttemptRecord:
CreateEmptyAttemptRecord = lngResult
Exit Function
Err_CreateEmptyAttemptRecord:
Err.Clear
lngResult = gd_NULLLONG
Resume Exit_CreateEmptyAttemptRecord
End Function
Private Function GenRandomLong() As Long
Const NINEHUNDRED_MILLION As Long = 900000000
Const ONEHUNDRED_MILLION As Long = 100000000
Randomize ' Initialize random-number generator.
GenRandomLong = CLng((NINEHUNDRED_MILLION * Rnd) + ONEHUNDRED_MILLION)
End Function
Private Function InsertBlankAttemptRecord(id As Long) As Boolean
On Error GoTo Err_InsertBlankAttemptRecord
Dim blnResult As Boolean
Dim strSQL As String
Dim cmd As ADODB.Command
Dim cnn As ADODB.Connection
blnResult = True
strSQL = "INSERT INTO Attempts (AtID, CustomerID, SeqNumber, PgmID,
NewPgmID, AttemptNumber, " & _
"RemoteCallTime, AttemptDate, AttemptTime, PhoneNumberDialed,
ResultID, CallerID," & _
"Incomplete, MadeContact) " & _
"VALUES (" & _
id & "," & _
gd_NULLLONG & "," & _
gd_NULLLONG & "," & _
gd_NULLLONG & "," & _
gd_NULLLONG & "," & _
gd_NULLLONG & "," & _
"#" & gd_NULLDATE & "#," & _
"#" & Date & "#," & "#" & _
Time() & "#," & "'" & _
gd_NULLSTR & "'," & gd_NULLLONG & _
"," & gd_NULLLONG & _
"," & "True," & _
"False);"
Set cnn = New ADODB.Connection
Set cmd = New ADODB.Command
cnn.Open gd_ADOconnstr
Set cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
cmd.CommandTimeout = ADOTIMEOUTSECONDS
cmd.Execute
cnn.Close
blnResult = True
Exit_InsertBlankAttemptRecord:
InsertBlankAttemptRecord = blnResult
Exit Function
Err_InsertBlankAttemptRecord:
Err.Clear
cnn.Errors.Clear
blnResult = False
Resume Exit_InsertBlankAttemptRecord
End Function
Message #5 by "David Chapman" <luckychap@b...> on Tue, 25 Dec 2001 05:20:37 +1030
|
|
I gave up auto numbers because it was to easy to cause "missing records"
which have to be explained to users and auditors. I use a simple table with
one record only and several number fields, as my apps require several series
of numbers. A function receives the name of the field, increments it, and
returns a formatted string prefixed with the code that indicates the type of
transaction - eg R = receipt, P = payment, I = invoice, J = journal.
Speed - I can't tell the difference from an auto number field.
Also there is an advantage in knowing the number to update associated tables
instead of having to read a new record to obtain the autonumber.
David
----- Original Message -----
From: "Rory" <mcneillr@c...>
To: "Access" <access@p...>
Sent: Sunday, December 23, 2001 4:30 AM
Subject: [access] Re: Better way to generate primary keys?
> > In A Nutshell:
> >
> > I'm looking for suggestions on a better way to handle
> > the generation of primary key values for a multi-user
> > MS Access/VBA environment.
> >
> > The Details:
> <snip>
>
> Is there any need for the numbers to be sequential? If you don't mind
> your key table growing, you could populate it with keys as they are
> generated, and check that a new key doesn't already exist. The table,
> consisting of only the long KeyID, wouldn't be all that large relative to
> the table(s) it is supporting. But this would encounter the same
problems!
>
> You may wish to pass the function a table name for it to check against -
> it could check the new key is not yet being used.
>
> Finally, with 6-7 users you may be able to avoid going across the network
> by preallocating number ranges to each user. You'd need a table local to
> each user to coordinate this. A related approach that might work (more
> complicated, though) would be to give out a batch (say 10) of numbers each
> time the function is called, and to have each user's process track them as
> they're used (static array of some sort?). Getting really complicated you
> could have a background process checking the number of unallocated
> numbers, and getting a new batch before the old one expires - in essence a
> buffer process.
>
> Yet one more idea - instead of giving a random number immediately as you
> do, you might pause (eg. loop) a few milliseconds and try again, giving
> the random number only when you've tried several times. This is doing
> some of the OS's work for it, but the users may prefer to wait a bit more
> rather than get a number that confuses them.
>
> Hope these ideas might bear fruit for you!
> Rory
> Rockhampton, Queensland, Australia.
>
>
|
|
|
View Cart
