Solved

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

Posted on 2003-11-08
10
345 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:rayglynn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 1

Expert Comment

by:fsaims
ID: 9707416
Hi Buddy! Can u write the connectivity code here for examination?
0
 
LVL 3

Expert Comment

by:tonydspaniard
ID: 9707547
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
 
LVL 3

Expert Comment

by:tonydspaniard
ID: 9707550
to check out whether i am right or not.. track the error and check if always comes from same user..
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rayglynn
ID: 9710797
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
 
LVL 9

Accepted Solution

by:
Valliappan AN earned 250 total points
ID: 9711441
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
 

Author Comment

by:rayglynn
ID: 9714197
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
 
LVL 6

Expert Comment

by:VK
ID: 9715185
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
 

Author Comment

by:rayglynn
ID: 10484862

Set cmdChange = Nothing. did the trick ok

many thanks for all your help

Ray
0

Featured Post

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question