FredixBlog

Quertion: I am trying to create a custom autonumber, due to an upgrade from a previous approach database that used a double as the autonumber and primary key.

Got to the first session, paused. Try to contimue with F8. The record has been locked by the other session, the record can’t anymore be updated with a pessimistic lock.

here is the code I used to generate the autonumber. The table I created contains only one field (autonum).

This will be used in a multi-user environment in Access 2000 SR-1 runtime
and I need advice on how I can ensure an error will not kick the user out.

I tried to simulate simultaneous use but did not get an error as I have
expected to.

Is their a better way to test it before finding out the hard
way, it don’t really work?

Is their a better way of doing this?

Any suggestions will be appreciated.

Here’s my code

Module Code:
Public Function GetAutoNum()
‘Returns Job Number from autonum table in autonum field.
‘then adds one to autonum.
Dim num As Double
Dim rst As ADODB.Recordset
    Set rst = New ADODB.Recordset
    rst.Open “autonum”, CurrentProject.Connection, adOpenDynamic,
    adLockPessimistic
    rst.MoveFirst
    num = rst(”autonum”)
    rst(”autonum”) = num + 1
    rst.Update
    rst.Close
    Set rst = Nothing
    GetAutoNum = num
End Function

Form Code:
Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.jobnum = work.GetAutoNum
End Sub

Thanks in advanced
-Adam

With a regular lock, this is the second session that will fail to update.

So, here the locking method doesn’t matter, but you have to add error trapping code according to your locking scheme. When failed, the rst.update should retry and the appropriate code should be added. Here the code for retries you will add shall work on both lock schemes.

Regards.

atrout77 asked this follow-up question on 6/5/2002:
Thank you, this is the type of answer I hoped to get. I knew I should have gotten an error when i tried to simulate the situation, but for some reason or another it didn’t
(I tried to have another user insert a record at the exact time I added it, probably didn’t do it at the exact same time so no error occured as I expected)
Fredix gave this response on 6/6/2002:
Yes, try it. Your code just need a few more lines. Tell me how it goes.

I will try this as it is a great suggestion, just wonder why I didn’t think of it, thanx again.

 

Also, in my previous post, replace pessimistic by optimistic.

The optimistic lock fires a lock for the user who first updates the record.

With your code, the lock method doesn’t makes any difference, as the transaction should be very quick.

atrout77 asked this follow-up question on 6/10/2002:
Ok, I tried what you suggested and recieved an error as i should have, so now I can trap it.

I however never really wrote code to perform a retry of something and was wondering if i should use a loop (To time the next retry) and retry then or it their a better way?

Can you give an example?

Thanks again,
-Adam

Fredix gave this response on 6/10/2002:
Sure,

Yes, this is just a small loop to write. Here is one, which will replace the rst.Update line:

Dim Retries as Integer
Dim OtherError as string
…
    Retries = 0
    On Error Resume Next ‘ No break when error occurs
    rst.Update
    While Err <> 0 and Retries < 10 ‘ We will retry 10 times
        Retries = Retries + 1
        rst.Update
        OtherError = Error$
    Wend
    On Error Goto 0 ‘ Back to normal error break
    If Retries = 10 then
        Msgbox “Update was unsuccessful.”, OtherError
    EndIf

10 retries is a very large number. 3 could be enough, it all depends on the potential number of users.
I added the OtherError variable, in case you have another error (disk full, disk error, duplicate record, etc)