Code Failure on Split Database

Meltdown

Registered User.
Local time
Today, 08:55
Joined
Feb 25, 2002
Messages
472
Hi everyone, I have the following code which generates an autonumber. This worked fine before I split my database, but now fails and my error code runs that it can't get a Counter.

The problem seems to be with the locking:
If I change:
Set rst = db.OpenRecordset("tblFlexAutoNum", _
dbOpenTable, dbDenyWrite + dbDenyRead)

to...

Set rst = db.OpenRecordset("tblFlexAutoNum")

...then it works...but obviously without the locking which I want because it's deployed in a multi-user environment.

Can anyone tell me why it fails on a split database but runs OK when the db is not split?

Can anyome tell me a solution so i can implement the locking on the table?

I have checked the ADO/DAO references on the back-end and everything looks OK

Thanks for any help
__________________________________________________________


Code:
Public Function acbGetCounter() As Long
    ' Get a value from the counters table and
    ' increment it
    
    Dim varField As String
    varField = Screen.ActiveForm.Tag
 
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim intLocked As Integer
    Dim intRetries As Integer
    Dim lngTime As Long
    Dim lngCnt As Long
    
    ' Set number of retries
    Const conMaxRetries = 5
    Const conMinDelay = 1
    Const conMaxDelay = 10
    
    On Error GoTo HandleErr
    
    Set db = CurrentDb()
    intLocked = False
    
    Do While True
        For intRetries = 0 To conMaxRetries
            On Error Resume Next
           Set rst = db.OpenRecordset("tblFlexAutoNum", _
           dbOpenTable, dbDenyWrite + dbDenyRead)
            If Err = 0 Then
                intLocked = True
                Exit For
            Else
                lngTime = intRetries ^ 2 * _
                 Int((conMaxDelay - conMinDelay + 1) * Rnd + conMinDelay)
                For lngCnt = 1 To lngTime
                    DoEvents
                Next lngCnt
            End If
        Next intRetries
        On Error GoTo HandleErr
        
        If Not intLocked Then
            If MsgBox("Could not get a counter: Try again?", _
             vbQuestion + vbYesNo) = vbYes Then
                intRetries = 0
            Else
                Exit Do
            End If
        Else
            Exit Do
        End If
    Loop
    
    If intLocked Then
        acbGetCounter = rst(varField)
        rst.Edit
         rst(varField) = rst(varField) + 1
        rst.Update
        rst.Close
    Else
        acbGetCounter = -1
    End If
    Set rst = Nothing
    Set db = Nothing
   
ExitHere:
    Exit Function
   
HandleErr:
    MsgBox Err & ": " & Err.Description, , "acbGetCounter"
    Resume ExitHere
End Function
 
Last edited by a moderator:
I don't think you can use the dbOpenTable on linked tables, try dbOpenDynaset, or just withoug that option.
 
Roy...you are a genius...dbOpenDynaset is the solution, thanks a lot for your hellp.

Regards
David
 

Users who are viewing this thread

Back
Top Bottom