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
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
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
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..
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(lStfI D 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
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
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_
Resume CleanExit
End Function
--------------------------
another update function:
Public Function UpdateScreenNotifies(lStfI
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
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
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_
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
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
Else
' unknown...
Call goError.LogError("Unknown Database Error", gDBConnection.Errors.Item(
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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.
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.
ASKER
Set cmdChange = Nothing. did the trick ok
many thanks for all your help
Ray