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
338 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
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
 

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now