VB creates AutoNumber

[zevw]

I am creating my own autonumber via code

BatchNo = Nz(DMax("BatchNo", "POS"), 0) + 1

look at thread705-1257165

The issue that I have is, this is a multi user environment and twice already 2 users ran the code exactly at the same time and got the same number. What can I do, to avoid that from happening?

 

[Eupher]

zevw,

Have a look at faq700-184.

HTH,

Ken S.

 

[zevw]

Ken!

Thanks

I need some help here, I am testing the code from the FAQ on one machine and using the front end of the program in 2 different directories. I am logging in as 2 different users and want to see if while one user is running the code will the other user get the error that they cannot read the table. Well I ran one user, and as soon as I passed the dbDenyRead I stop my code and run the second user. The second user did not get an error that the table is locked.

Facts of what I am doing the tblBatch (repalced tblNewQiNu)m table is not an actual table in the front end, it is linked. Would that make a difference? I need to have it linked, because all users are writing to the same database.

Your help will be highly appreciated.

This is my code

Public Function NewBatch_Num() As Long

    On Error GoTo NewBatchNum_Err

    Dim MyDb As Database        'Substitute Your Database Name Here
    Dim BatchData As Recordset
    Dim tblBatch As Recordset
    Dim SqlBatchMax As Recordset

    'Constants for the expected errors
    Const RiErr = 3000
    Const LockErr = 3260
    Const InUseErr = 3262
    Const NumReTries = 20#          'This is just a number of attempts

    'Variables for the Retry count
    Dim NumLocks As Integer
    Dim lngX As Long
    Dim BatchNum As Long
    Dim OldBatchNum As Long
    Dim NewBatchNum As Long

    'Remember to Use your DataBase Name
    Set MyDb = CurrentDb()
    Set BatchData = MyDb.OpenRecordset("POs")

    SqlTxt = "SELECT Max(POs.BatchNo) AS Batch FROM POs;"

    Set SqlBatchMax = MyDb.OpenRecordset(SqlTxt, dbDenyRead)
    Set tblBatch = MyDb.OpenRecordset("BatchNos", dbDenyRead)

    OldBatchNum = SqlBatchMax!Batch                  'A
    NewBatchNum = tblBatch!BatchNo             'C
    If (OldBatchNum > NewBatchNum) Then
        NewBatchNum = OldBatchNum
    End If
    
    'Increment the ID
    NewBatchNum = NewBatchNum + 1

    'Update the Id value and (just for my own interest) the date/time stamp
    With tblBatch
        .Edit
            !BatchNo = NewBatchNum
        .Update
    End With
    NewBatch_Num = NewBatchNum

NormExit:
    Set BatchData = Nothing
    Set MyDb = Nothing

    Exit Function           'Return

NewBatchNum_Err:

    'This is the part where we wait if another user is getting a New ID

    'Check for the Expected errors
    If ((Err = InUseErr) Or (Err = LockErr) Or (Err = RiErr)) Then

        'If one of the expected ones, Increment the Counter
        NumLocks = NumLocks + 1

        If (NumLocks < NumReTries) Then     'Check for to many attempts
                                            'Failing here probably indicates
                                            'a system problem, not a real "TimeOut"

            'We generate a pseudo random value to use in the empty loop
            'it's really just a (pseudo) ramdom interval thinnnggggggyyyyyy
            For lngX = 1 To NumLocks ^ 2 * Int(Rnd * 20 + 5)
                DoEvents            'Wait in La-La land
            Next lngX
            Resume Next             'Go Back and Try Again
         Else
            'This should not happen.  20 Random retries should always
            'get a unique number.  But here is where the error handler would go
        End If
     Else
        'Unexpected Error - Also known as "Tell me the Story, the old, old story ...
        MsgBox "Error " & Err.Number & ": " & Err.Description, _
                vbOKOnly & vbCritical, "Get Batch Number"
        GoTo NormExit
    End If

End Function

 

[Eupher]

zevw,

I find with things like this it's often best to start with the simple then proceed to the complex. In other words, I would get the code working first with no linked tables just to rule out that as a possibility for problems. Once everything works with regular tables, go from there...

HTH,

Ken S.

 

[zevw]

I don't really understand what the need would be for having a autonumber created by VBA, when you do not have multiple users from different locations. My whole issue is that when I have 2 users or more running the same process, and trying to pick up the highest number, what will stop them from using the same number if the LINKED tables are not locked. Secondly if the table is built in to the database and we use sharing on the same front end, it drastically slows down the processes.

In Summary I would appreciate a solution where you can lock the linked table, and not let the second user have access to the next number while the first user is querying the table.

 

[Eupher]

Hi, zevw,

Okay, I have never used MichaelRed's technique. Maybe if Michael is lurking, he can pop in and offer some clarification. Having said that, I've examined this a little and I offer the following observations and suggestions:

1) I don't understand the purpose of the BatchData recordset (BaseData in Michael's FAQ). It's opened, but never referred to again except to close it.

2) After perusing the help files, I discovered some relevant info regarding recordsets and the dbDenyRead option:
a) If the Type argument of the OpenRecordset method is not specified, Access tries to open a table-type recordset if it can. For linked tables and queries, a dynaset-type recordset is opened.
b) This is significant because you are using linked tables, and the dbDenyRead option applies only to table-type recordsets. If you specify the dbOpenTable argument, the dbDenyRead option may then work.
c) In my experiments, trying to specify dbOpenTable for a query always raised an error.
d) The Options argument (where you would place dbDenyRead) is the 3rd argument of the OpenRecordset method, so there needs to be an extra comma between the source and option arguments (unless you are specifying the recordset type).
e) In my tests, the error that was raised when trying to access a table that was locked with dbDenyRead was 3009.

3) So, it seems likely that Michael's FAQ was written in an earlier version of Access/VBA and needs a bit of modification for later versions (not sure which versions are affected - I'm running 2003). I'm not sure how to address the dbDenyRead issue with queries, as it doesn't seem like you can open a table-type recordset from a query, and by extension, dbDenyRead is not available. You might experiment with opening a dummy one-record table recordset, specifying dbDenyRead, before opening any actual BatchNum recordsets, then closing it after you're all through getting your autonumber. Seems like that ought to work.

HTH,

Ken S.

 

[zevw]

Ken!

Thanks for all your research, I was very busy and did not have a chance to respond, but saw it right away and I"m very grateful.

However I tried your solution today, and maybe I not getting something this is what I tried.

 Set SqlBatchMax = MyDb.OpenRecordset(SqlTxt, , dbOpenTable, dbDenyRead)

I got Error 3001: Invalid Argument

What am I missing over here.

Thanks in advance!

William

 

[Eupher]

Hi, William,

I don't think you got all of my post:

c) In my experiments, trying to specify dbOpenTable for a query always raised an error.

Doesn't seem to matter whether it's a saved query or a SQL string - it will raise an error.

My suggestion again:

You might experiment with opening a dummy one-record table recordset, specifying dbDenyRead, before opening any actual BatchNum recordsets, then closing it after you're all through getting your autonumber.

By this I mean creating a simple table with only one record. The data in it is irrelevant, as its only purpose is to be open and locked at the start of your procedure!

HTH,

Ken S.