CMULHERON
asked on
Error -2147467254
I am trying to write a program for multiple users to take tests on the PC at the same time. I am getting an error in several different sections of my code that mention a record lock. I listed the error number above, but have had 3 different descriptions of the error including: "Could not lock file" and "The database has been placed in a state by user 'ADMIN' on machine 'ABC' that prevents it from being opened or locked"
I put in error handling code to find out what section this happens in. I am listing the code below. If anyone has any posible soltions I would sure love to hear them. Thanks
Public Sub Rtv_Mult_Answer()
Dim cn As ADODB.Connection
Dim rs_US As ADODB.Recordset
On Error GoTo errh:
Err_Cnt = 0
Set cn = New ADODB.Connection
With cn 'Open Connection
.ConnectionString = "Provider=Microsoft.Jet.OL
.CursorLocation = adUseClient
.Open
End With
Set rs_US = New ADODB.Recordset
With rs_US 'Write user answer to User score file
If frmUser.Del_W <> "C" Then ' Not Completed get prev answer
.Filter = "USUSER = '" & frmUser.UserID_W & _
"' and USTSTNUM = " & frmUser.Tstnum_W & _
" and USTSTQST = " & frmUser.TstQst_W & _
" and USSEQNUM = " & frmUser.SeqNum_W & _
" and USDTES = " & Date
Else 'User took test before get last tests answers
.Filter = "USUSER = '" & frmUser.UserID_W & _
"' and USTSTNUM = " & frmUser.Tstnum_W & _
" and USTSTQST = " & frmUser.TstQst_W & _
" and USSEQNUM = " & frmUser.SeqNum_W & _
" and USDTES = " & frmUser.LastDate_W
End If
.Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
frmUser.PrevAns_W = !USANSNUM
.MoveNext
End With
rs_US.Close 'Close User Score File
Set rs_US = Nothing
cn.Close 'Close the Connection
Set cn = Nothing
Exit Sub 'Don't do this unless error happens
errh:
' Retry until MAX_RETRIES are hit to increment your error count.
Err_Cnt = Err_Cnt + 1
If Err_Cnt < 20 Then
MsgBox "Error in RTV_MULT_ANSWER", vbCritical
Resume
Else
' Retries did not help. Show the error, and fall out.
MsgBox Err.Number & " " & Err.Description & " Contact technical support with this information", vbCritical
End
End If
End Sub
I put in error handling code to find out what section this happens in. I am listing the code below. If anyone has any posible soltions I would sure love to hear them. Thanks
Public Sub Rtv_Mult_Answer()
Dim cn As ADODB.Connection
Dim rs_US As ADODB.Recordset
On Error GoTo errh:
Err_Cnt = 0
Set cn = New ADODB.Connection
With cn 'Open Connection
.ConnectionString = "Provider=Microsoft.Jet.OL
.CursorLocation = adUseClient
.Open
End With
Set rs_US = New ADODB.Recordset
With rs_US 'Write user answer to User score file
If frmUser.Del_W <> "C" Then ' Not Completed get prev answer
.Filter = "USUSER = '" & frmUser.UserID_W & _
"' and USTSTNUM = " & frmUser.Tstnum_W & _
" and USTSTQST = " & frmUser.TstQst_W & _
" and USSEQNUM = " & frmUser.SeqNum_W & _
" and USDTES = " & Date
Else 'User took test before get last tests answers
.Filter = "USUSER = '" & frmUser.UserID_W & _
"' and USTSTNUM = " & frmUser.Tstnum_W & _
" and USTSTQST = " & frmUser.TstQst_W & _
" and USSEQNUM = " & frmUser.SeqNum_W & _
" and USDTES = " & frmUser.LastDate_W
End If
.Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
frmUser.PrevAns_W = !USANSNUM
.MoveNext
End With
rs_US.Close 'Close User Score File
Set rs_US = Nothing
cn.Close 'Close the Connection
Set cn = Nothing
Exit Sub 'Don't do this unless error happens
errh:
' Retry until MAX_RETRIES are hit to increment your error count.
Err_Cnt = Err_Cnt + 1
If Err_Cnt < 20 Then
MsgBox "Error in RTV_MULT_ANSWER", vbCritical
Resume
Else
' Retries did not help. Show the error, and fall out.
MsgBox Err.Number & " " & Err.Description & " Contact technical support with this information", vbCritical
End
End If
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you are using a restrictive connection an recordset there, try using a less restrictive one
.Open "USRSCR", cn, adOpensnapshoot, adLockoptimistic, adCmdTable
(if i remember well)
.Open "USRSCR", cn, adOpensnapshoot, adLockoptimistic, adCmdTable
(if i remember well)
ASKER
The cursor options are opendynamic, openstatic, openkeyset and openforwardonly. The lock types available are lockBatchoptimistic, lockOptimistic, lockPessimistic and lockReadolny. I don't think that Opensnapshoot is an option. By the way I am using VB6.0
I am using the forwardonly cursor because I only need to read the records in order and I am using the LockReadonly becaue I don't need to update anything, so I figured that VB would not lock the file then.
.Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
In my last post I mentioned I missed closing the connection a couple times. Would this have caused the problem?
I am using the forwardonly cursor because I only need to read the records in order and I am using the LockReadonly becaue I don't need to update anything, so I figured that VB would not lock the file then.
.Open "USRSCR", cn, adOpenForwardOnly, adLockReadOnly, adCmdTable
In my last post I mentioned I missed closing the connection a couple times. Would this have caused the problem?
I recommend changing your cursor to a static, potentially changing your cursor location type (on the connection open) to useServer.
This should work for you.
This should work for you.
ASKER
Here is Microsoft's defintion of the forward only cursor.
"Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset"
As for the cursor location on the connection, the book I have says that you cannot sort when usinf the USESERVER cursor and I need to sort some of my data. It also says that the only time you should need to use the ADUSESEVER cursor is for batch transactinos, and mine is interactive.
So I don't think either of these will work for me, but thanks for your input, and if you have any other sugesstions it sure would help. Thanks
"Default. Uses a forward-only cursor. Identical to a static cursor, except that you can only scroll forward through records. This improves performance when you need to make only one pass through a Recordset"
As for the cursor location on the connection, the book I have says that you cannot sort when usinf the USESERVER cursor and I need to sort some of my data. It also says that the only time you should need to use the ADUSESEVER cursor is for batch transactinos, and mine is interactive.
So I don't think either of these will work for me, but thanks for your input, and if you have any other sugesstions it sure would help. Thanks
ASKER
Thanks Richie - I went through my code and found that I did not close the database in a couple spots. I posted a new question if you have any ideas.
Glad to help but, why a "B" grade?
ASKER
I don't know, This is my first time posting and I didn't really pay much attention to the grading system. Sorry
ASKER
I did go through the rest of my code and did find 2 spots that I did not close the connection "CN.close" before I left the subroutine. I use CN as my connection in all my subroutines. Could this be the cause?
Also, when I find and error I do an "Exit Sub". I just close the connection in this case. Do I need to set my recordsets = NOTHING before closing the connection or will closing the connection do this for me?