Link to home
Start Free TrialLog in
Avatar of rayglynn
rayglynn

asked on

Using VB6, an Access DB and ADODB, What is the correct method of handling DB locking in a network?

Hi,
I have an application which runs on a network sharing a common database.
It's written in VB6 using ADODB for connectivity to an Access 2000 DB.
It runs on 4 PCs and about once or twice a week I get the following error (or similar)

-2147467259 The table 'tblStaff' is already opened exclusively by another user,
 or it is already open through the user interface and cannot be manipulated programmatically.

Im using Optimistic locking.

If I get a DB related error I resume for 5 times on the failing command in the hope that the lock will be released by then but this doesnt catch the above error.

Can any one tell me the correct method of handling table/record locking using ADODB ?
(I've used DAO in the past and I rearly had problems)

Peculiarily enough, this error only hanppens on one PC (the fastest one on the network).

Any help with this would be much appreciated.

Thanks

Ray Glynn
Avatar of fsaims
fsaims

Hi Buddy! Can u write the connectivity code here for examination?
check this my friend:

if a user logs into the PC running the application and he/she does not have LAN permissions to create files in the directory where your .mdb lives then they cannot create the .ldb file so it will only open exclusive because it needs the .ldb to track record locks. The .ldb file is created / deleted automatically by ms access.

kind Regards and Good luck
to check out whether i am right or not.. track the error and check if always comes from same user..
Avatar of rayglynn

ASKER

Hi thanks for your replies so far
The problem is associated with 1 PC but Ill check and make sure its the same user logging on to it. The application works fine normally but ocassionally it comes up with this error and then can settle down again and carry on ok.

Its a call logging application. Calls are entered into the DB and assigned to users. each users program on a 30 secont timer event check the DB for pending Calls.

Im not sure which functions are causing the problem but below are some functions in the general area showing the connectivity code.

Thanks for any more help you can give with this....

Ray

----------------------------------------------------------------
update function:

Public Function bSetNewCallFlag(lCallID As Long, bCallState As Boolean)
Dim cmdChange As ADODB.Command
   
    On Error GoTo ErrorHandler
   
    Set cmdChange = New ADODB.Command
    Set cmdChange.ActiveConnection = gDBConnection
   
    cmdChange.CommandText = "Update tblCalls SET CallNewCall = " & Str$(bCallState) & " WHERE [ID] = " & Str$(lCallID)
    cmdChange.Execute

    bSetNewCallFlag = True

CleanExit:
    Exit Function
   
ErrorHandler:
    If CheckDBError() Then Resume 0
    Call goError.LogError(msModule_Name, "bSetNewCallFlag", Err.Number, Err.Description, LogType.LogUnEx)
    Resume CleanExit
   
End Function

-------------------------------------------------------------------
another update function:

Public Function UpdateScreenNotifies(lStfID As Long)
Dim cmdChange As ADODB.Command
Dim rs As ADODB.Recordset
   
    On Error GoTo ErrorHandler
   
    ' first add up the number of outstanding messages to be shown
   
    Set rs = New ADODB.Recordset
    rs.Open "Select Count(*) as [MsgCount] from [tblCalls] WHERE CallNewCall = true AND CallScreen = true AND [StaffID] = " & Str$(lStfID), gDBConnection
    If Not (rs.EOF And rs.BOF) Then
       
        Set cmdChange = New ADODB.Command
        Set cmdChange.ActiveConnection = gDBConnection
       
        If rs("MsgCount") > 0 Then
            cmdChange.CommandText = "Update tblStaff SET StaffCallNotify = true WHERE [ID] = " & Str$(lStfID)
        Else
            cmdChange.CommandText = "Update tblStaff SET StaffCallNotify = false WHERE [ID] = " & Str$(lStfID)
        End If
       
        cmdChange.Execute
   
    End If
   
    If rs.State <> adStateClosed Then rs.Close
    Set rs = New ADODB.Recordset
    rs.Open "Select Count(*) as [MsgCount] from [tblAppointments] WHERE AppNewApp = true  AND AppScreen = true AND [StaffID] = " & Str$(lStfID), gDBConnection
    If Not (rs.EOF And rs.BOF) Then
       
        Set cmdChange = New ADODB.Command
        Set cmdChange.ActiveConnection = gDBConnection
       
        If rs("MsgCount") > 0 Then
            cmdChange.CommandText = "Update tblStaff SET StaffAppNotify = true WHERE [ID] = " & Str$(lStfID)
        Else
            cmdChange.CommandText = "Update tblStaff SET StaffAppNotify = false WHERE [ID] = " & Str$(lStfID)
        End If
        cmdChange.Execute
   
    End If
   
    UpdateScreenNotifies = True

CleanExit:
    If rs.State <> adStateClosed Then rs.Close
    If Not rs Is Nothing Then Set rs = Nothing
    Exit Function
   
ErrorHandler:
    If CheckDBError() Then Resume 0
    Call goError.LogError(msModule_Name, "UpdateScreenNotifies", Err.Number, Err.Description, LogType.LogUnEx)
    Resume CleanExit

End Function
-------------------------------------------------------------------
DB Error checking function called in each error handler:

Public Function CheckDBError()
   
   
    CheckDBError = True
    ' default to true
    ' only false when retries are exhausted
   
    If gDBConnection.Errors.Count > 0 Then
       
        LogMsg "", "Database Error," & Err.Number & "," & Err.Description
        ' write the error to the audit file as well
        ' also by reading the error it clears it
       
        iErrCount = iErrCount + 1
        ' keep counting the errrors
       
       
        If iErrCount >= 5 Then
        ' Error hasnt cleared so report it and finish retrying
       
            If Err > 2999 And Err < 3752 Then
            ' range of valid db error messages
                Call goError.LogError("Database Error", "", Err.Number, Err.Description, LogType.LogUnEx)
            Else
            ' unknown...
                Call goError.LogError("Unknown Database Error", gDBConnection.Errors.Item(0), Err.Number, Err.Description, LogType.LogUnEx)
            End If
           
            CheckDBError = False
            ' this will stop it coming back into CheckDBError()
           
            Exit Function
           
        End If
       
    Else
        CheckDBError = False
    ' if its not a DB error then we dont want to resume on the error line
    ' we just want to carry on and report the error
   
    End If

End Function
ASKER CERTIFIED SOLUTION
Avatar of Valliappan AN
Valliappan AN
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi,
Thanks for further suggestion
I think you're on the right track here...

I presume if I execute the query direct then there's nothing to close or set = nothing

Im going to try this now.. but it will take a week or 2 to know if its working.

Thanks for your help

Cheers,

Ray
Hello rayglynn !

You said:

It runs on 4 PCs and about once or twice a week I get the following error (or similar)
...
If I get a DB related error I resume for 5 times on the failing command in the hope that the lock will be released by then but this doesnt catch the above error.

1. Does the error vanish if you wait for an adequate time and repeat the critical command?

1a. No. This is not a matter of locking. It's probably a bug in code.
1b. Yes. When you trap into the error handler do a doevents and resume until the program can be continued.
            Perhaps the fast PC is to fast an made more than 5 requests while an other recordset is open in edit mode (optimistic).
            You won't get an infinite loop if it is a locking problem.

V.K.






Set cmdChange = Nothing. did the trick ok

many thanks for all your help

Ray