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
rayglynnAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

fsaimsCommented:
Hi Buddy! Can u write the connectivity code here for examination?
0
tonydspaniardCommented:
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
0
tonydspaniardCommented:
to check out whether i am right or not.. track the error and check if always comes from same user..
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

rayglynnAuthor Commented:
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
0
Valliappan ANSenior Tech ConsultantCommented:
After every time, you open cmdChange, you might need to set it to Nothing.

Set cmdChange = Nothing.

Have a look at a similar issue here:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/Q234/2/18.asp&NoWebContent=1

I'd suggest to execute query direct, without command object, like this:
gDBConnection.Execute 'your query here'

You might also check the Err.Number property for -2147467259, and may proceed accordingly.

Hope, it helps. Cheers.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
rayglynnAuthor Commented:
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
0
VKCommented:
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.





0
rayglynnAuthor Commented:

Set cmdChange = Nothing. did the trick ok

many thanks for all your help

Ray
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.